VBA: How do you declare range with multiple non-consecutive columns?

[shane_aldrich]

Hi All,

THis is more of a syntax question. If you look at the string below, when I try to SET rng1 is where I'm havning trouble, If I set using the column letter, that works just fine, I'd like to know the syntax on how to set the same range, but using column numbers, next to rng1 is a comment that select A:O, just not sure how to add the others?....Thank you!!!


Sub Summary_Disconnect()
'*************************
'*** Declare Variables ***
'*************************
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lLastRow As Long
Dim lLastCol As Long

Dim rng1 As Range
Dim rng2 As Range

'*********************
'*** Set Variables ***
'*********************

Set ws1 = Sheets("Disconnect_Compile")

ws1.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Summary_Disconnect"
Set ws2 = Sheets("Summary_Disconnect")

lLastRow = Last(1, Columns(1))
lLastCol = Last(2, Rows(1))
Set rng1 = ws2.Range("A:O,Q:R,U:AA,AI:AN,AU:AU") ' Set rng1 = ws2.Range(ws1.Columns(1), ws2.Columns(14))
Set rng2 = Range(Cells(4, 2), Cells(lLastRow, 2))

 

[andrewkard]

Hello, try use Option Explicit before Sub. What operator is Last? Maybe you want something like that:

LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column</SPAN>
LastRow = Cells(Rows.Count, "A").End(xlUp).Row</SPAN>

 

[Firefly2012]

Hi

You could Union one contiguous column range to another contiguous column range (not necessarily contiguous together) and so on. AFAIK there is no way to do it like you can with column letters though - unless you specifically built up the address string to pass to Range (but this seems like a lot of effort).

 

[shane_aldrich]

I'm a newbie, had been relying on recorder forever, can you give me an example of how you perform the union?

 

[Firefly2012]

Sure:

Dim rng As Range

Set rng = Range(Cells(1,1),Cells(Rows.Count,14))

Set rng = Application.Union(rng, Range(Cells(1,17),Cells(Rows.Count,20))

Set rng = Application.Union(rng, Range(Cells(1,23),Cells(Rows.Count,30))

'etc etc

So you build up the range with contiguos elements at each use of Union