Asked by:
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" ' add cell to LowRange End If End Sub
 Edited by VBNovice01 Friday, January 20, 2017 10:22 AM
All replies



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 IfRegards, OssieMac

Additional info that might help
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 IfRegards, OssieMac


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
MsgBox cel.Address
Next celRegards, OssieMac