# How do you add a cell to a range • ### Question

• Feel like an idiot for asking this, but how on earth do you add a cell to a range?

Ive declared range

I want to add a particular cell to that range?

How?

```Sub AddtoRange()

Dim HighRange As Range
Dim LowRange As Range
Dim WholeRng As Range
Dim i, j As Integer

i = 1 '1 down
j = 2 'column 2 ("B")

Set WholeRng = Range("B1:B26")

For Each Cell In WholeRng
If Cell.Value = "High" Then
'use negative y to offset left
Cells(i, j).Offset(0, -1) = "1 left"
' add cell to HighRange in other words add A1 to High Range if B1 has the value "high"

ElseIf Cell.Value = "Low" Then
Cells(i, j).Offset(0, -1) = "1 left"

End If

End Sub```

• Edited by Friday, January 20, 2017 10:22 AM
Friday, January 20, 2017 10:14 AM

### All replies

• Dim rng As Range

Set rng = Range("A1:A20")

Set rng = Union(rng, Range("A21"))

Note that the added cell or range can be any cell or range; not necessarily an adjacent cell or range.

Regards, OssieMac

Friday, January 20, 2017 10:22 AM
• how can i make it so that it is the adjacent cell or the one I want?
Friday, January 20, 2017 10:23 AM
• Appears that you were editing your original post while I was answering.

I am not sure exactly what you are attempting to do but the Union function allows multiple ranges to be assigned to one range variable.

There is a caveat. You need to ensure that the range variable actually has an initial range assigned to it before the Union function can be used.

Dim rng As Range

Set rng = Range("A1:A20")

'If not nothing then already has a range assigned
'so use Union to add a range to it
If Not rng Is Nothing Then
Set rng = Union(rng, Range("A21"))
Else
'If is nothing then no existing range assigned
'so cannot use union so just assign the range on its own
Set rng = Range("A21")
End If

Regards, OssieMac

Friday, January 20, 2017 10:36 AM
• If I am interpreting your code correctly then like the following (but untested)

If cell.Value = "High" Then
'use negative y to offset left
If Not HighRange Is Nothing Then
Set HighRange = Union(cell, Cells(i, j).Offset(0, -1))

Else
'If is nothing then no existing range assigned
'so cannot use union so just assign the range on its own
Set HighRange = Cells(i, j).Offset(0, -1)
End If
End If

Regards, OssieMac

Friday, January 20, 2017 10:45 AM
• if I want to copy HighRange to Sheet 2 to ensure Ive got the correct outcome

im trying this

`Worksheets("Sheet2").Range("A1").Value = HighRange.Value`

Friday, January 20, 2017 11:32 AM
• You can copy a split range if the split range creates a regular shape of rows by columns but if it creates a staggered range then it will not copy and paste.

Example:

The following will copy and paste because it is 2 regular shapes that can be put together to create one regular shape but it does not paste to the corresponding columns of A and D; it pastes to columns A and B as a contiguous range.

Set HighRange = Range("A1:A4")
Set HighRange = Union(HighRange, Range("D1:D4"))
HighRange.Copy Destination:=Worksheets("Sheet2").Range("A1")

The following will not copy and paste because the union is a staggered range but the union range is quite valid and you can iterate through all of the cells in the union.

Set HighRange = Range("A1:A4")
Set HighRange = Union(HighRange, Range("D4:D8"))
HighRange.Copy Destination:=Worksheets("Sheet2").Range("A1")

Iterate through the range as per the following example. The MsgBox displays the address but you can also retrieve the value (using cel.Value) in lieu of the address.

Dim HighRange
Dim cel As Range
Set HighRange = Range("A1:A4")
Set HighRange = Union(HighRange, Range("D4:D8"))
For Each cel In HighRange