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

shane_aldrich

Board Regular
Joined
Oct 23, 2009
Messages
148
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))
 

Some videos you may like

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

andrewkard

Active Member
Joined
Apr 6, 2012
Messages
455
Hello, try use Option Explicit before Sub. What operator is Last? Maybe you want something like that:
Code:
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column</SPAN>
LastRow = Cells(Rows.Count, "A").End(xlUp).Row</SPAN>
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
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

Board Regular
Joined
Oct 23, 2009
Messages
148
I'm a newbie, had been relying on recorder forever, can you give me an example of how you perform the union?
 

Firefly2012

Well-known Member
Joined
Dec 28, 2011
Messages
3,638
Sure:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top