Combining Ranges in VBA or Excel

[Izzy600]

Hi Everyone!

I'm looking for a way to combine vertical ranges of data in excel in to 1 range table. this could either be in Excel for VBA.

For example, if Rng1 = A1:A7, Rng2 = B1:B7 and Rng3 = C1:C7, I want to create a range the combines Rng1, Rng2 and Rng3 into TblRng = A1:C7. I tried using the Union function but that only seems to work for making a vertical array instead of a range.

Any help would be *hugely* appreciated!!

Thank you

 

[shg]

Perhaps

Union(Rng1, Rng2, Rng3).Name = "tblRng"

 

[Izzy600]

Thanks so much for the response, but I don't think that will work. I need a variable in my vba code that is a combination of Rng1, Rng2 and Rng3.

I tried using Set TblRg = Union(Rng1,Rng2,Rng3) but that only works if the ranges are next to each other. So it works for the previous example I gave but not if Rng1 = A2:A7, Rng2 = A8:A13, Rng3 = A14:A19. If I combine the ranges when they're situated like that in my Excel spreadsheet, then I get a range that has 18 rows and 1 column instead of 6 rows and 3 columns.

 

[shg]

Defining a name for a range doesn't change its shape; it is what it is on the worksheet.

That shouldn't be an obstacle to doing whatever it is you want to do, which is ...?