Multiple, non-contiguous, column selection using column numbers

[polarisking]

Say I want to do something with columns 2 through 5. Is there a way to specify Columns(2:5) or something similar?

What about a set of non-contiguous groups like 2-5, 7, 8-11, and 15? (can this be done with letters?)

Thanks in advance.

 

[Keetoowah]

Hi polarisking
for contiguous columns you can use

Columns(3).Resize(, 3).Select
'columns 3 to 5

or

Range(Columns(3), Columns(5)).Select

For non contiguous columns

Union(Columns(3), Columns(5), Columns(7)).Select

 

[polarisking]

Very helpful. What does the Resize do?

 

[p45cal]

With your example 2-5, 7, 8-11, and 15:
add some Cs in front, make those dashes colons leaving: C2:C5,C7,C8:C11,C15 (this is what R1C1 notation looks like where C means Column) then use it in the likes of, for example:
Range(Application.ConvertFormula("C2:C5,C7,C8:C11,C15", xlR1C1, xlA1)).Select
Check out the help on ConvertFormula, there's more to it.

Letter equivalent:
Range("B:E,G:G,H:K,O:O").Select

 

[Keetoowah]

What does the Resize do?

Hi polarisking,
thanks for your feedback. The Resize property in Excel VBA makes a range a specific number of rows (0 in this case where the value is omitted) and columns (3) larger (or smaller if you use negative numbers). For a more detailed explanation see here

 

[p45cal]

(or smaller if you use negative numbers)

You might struggle with that…

 

[Keetoowah]

Hello everyone,
I apologize for writing something wrong, thanks to p45cal for pointing this out, as indicated in the linked article we cannot use negative row & column number for RESIZE property.