Union Two With Ranges

[Guest]

How would I union two set of ranges with an example listed below.
Both Columns B and K start on row 2 since there is header information on
column 1.

Please help me with the code listed below.

Thanks,



With Range ("Staff_License").Range("b2:b")
With Range ("Staff_Codes").Range("k2:K")
For I = 2 to Row.count

Next
End With

Column B Column K
Staff License Staff Codes
0293 930090
6034 1237664
.... ...
7787 79289888

 

[RompStar]

look here:

http://msdn2.microsoft.com/en-us/library/aa213609(office.11).aspx

Worksheets("Sheet1").Activate
Set bigRange = Application.Union(Range("Range1"), Range("Range2"))
bigRange.Formula = "=RAND()"

 

[Guest]

Hi Dean,

Here is code that will Union 2 ranges but I am not sure that is what you
really need. See the second example of code below if what you need is to be
able to loop through the range in column B and be able to reference the
corresponding value in column K. It uses Offset from cells in column B to
reference column K.

Sub Create_Union()

Dim rngB As Range
Dim rngK As Range
Dim rngUnion As Range

With Sheets("Sheet1")
Set rngB = Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
Set rngK = Range(.Cells(2, "K"), .Cells(.Rows.Count, "K").End(xlUp))
Set rngUnion = Union(rngB, rngK)
End With

MsgBox rngUnion.Address

End Sub

Second Example:-

Sub Data_Offset_Demo()

Dim rngB As Range
Dim i As Long


With Sheets("Sheet1")
Set rngB = Range(.Cells(2, "B"), .Cells(.Rows.Count, "B").End(xlUp))
End With

'when a range is assigned to a range variable, the variable
'is like a mini worksheet within a worksheet and cells
'start as row 1, column 1 irrespective of the actual
'address in the worksheet.

With rngB
For i = 1 To .Rows.Count
MsgBox .Cells(i, 1).Value 'Values from col B
MsgBox .Cells(i, 1).Offset(0, 9).Value 'Values from col K
Next i
End With

End Sub

Regards,

OssieMac

 

[JW]

How would I union two set of ranges with an example listed below.
Both Columns B and K start on row 2 since there is header information on
column 1.

Please help me with the code listed below.

Thanks,

With Range ("Staff_License").Range("b2:b")
With Range ("Staff_Codes").Range("k2:K")
For I = 2 to Row.count

Next
End With

Column B Column K
Staff License Staff Codes
0293 930090
6034 1237664
... ...
7787 79289888

Kinda confused on your code. You have
Range("Staff_License").Range("B2:B"). You are referring to multiple
ranges in that statement. Do you mean
Sheets("Staff_License")>range("B2:B")? Also, you have no ending row
for your range argument. Need to set the last row via a variable or
hard code it.
If you did infact mean Sheets(...) instead of Range(...), I could be
mistaken, but I am pretty sure that your Union ranges have to live on
the same sheet.

 

[JW]

How would I union two set of ranges with an example listed below.
Both Columns B and K start on row 2 since there is header information on
column 1.

Please help me with the code listed below.

Thanks,

With Range ("Staff_License").Range("b2:b")
With Range ("Staff_Codes").Range("k2:K")
For I = 2 to Row.count

Next
End With

Column B Column K
Staff License Staff Codes
0293 930090
6034 1237664
... ...
7787 79289888

I could be mistaken, but I am pretty sure that your union ranges have
to live on the same sheet.