Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union Two With Ranges
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union Two With Ranges
look he
http://msdn2.microsoft.com/en-us/lib...ffice.11).aspx Worksheets("Sheet1").Activate Set bigRange = Application.Union(Range("Range1"), Range("Range2")) bigRange.Formula = "=RAND()" On Oct 17, 3:21 pm, Dean P. <Dean wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union Two With Ranges
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 "Dean P." wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union Two With Ranges
On Oct 17, 6:21 pm, Dean P. <Dean
wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Union Two With Ranges
On Oct 17, 6:21 pm, Dean P. <Dean
wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
union of named ranges based only on the names of those ranges | Excel Programming | |||
printing Union of Ranges | Excel Worksheet Functions | |||
Union of Ranges Failed | Excel Programming | |||
union method for non-adjacent ranges | Excel Programming | |||
VBA union of two ranges | Excel Programming |