VBA - Select columns using numbers?

Question

I'm looking for an alternative to this code, but using numbers. I want to select 5 columns, the start column is a variable, and then it selects 5 columns from this.

Columns("A:E").Select

How do I use integers instead, to reference columns? Something like below?

For n = 1 to 5
Columns("n : n + 4") .select
do sth
next n

Answer 1

You can use resize like this:

For n = 1 To 5
    Columns(n).Resize(, 5).Select
    '~~> rest of your code
Next

In any Range Manipulation that you do, always keep at the back of your mind Resize and Offset property.

Answer 2

Columns("A:E").Select

Can be directly replaced by

Columns(1).Resize(, 5).EntireColumn.Select

Where 1 can be replaced by a variable

n = 5
Columns(n).Resize(, n+4).EntireColumn.Select

In my opinion you are best dealing with a block of columns rather than looping through columns n to n + 4 as it is more efficient.

In addition, using select will slow your code down. So instead of selecting your columns and then performing an action on the selection try instead to perform the action directly. Below is an example to change the colour of columns A-E to yellow.

Columns(1).Resize(, 5).EntireColumn.Interior.Color = 65535

Answer 3

you can use range with cells to get the effect you want (but it would be better not to use select if you don't have to)

For n = 1 to 5
range(cells(1,n).entirecolumn,cells(1,n+4).entirecolumn).Select
do sth
next n

Answer 4

Try using the following, where n is your variable and x is your offset (4 in this case):

LEFT(ADDRESS(1,n+x,4),1)

This will return the letter of that column (so for n=1 and x=4, it'll return A+4 = E). You can then use INDIRECT() to reference this, as so:

COLUMNS(INDIRECT(LEFT(ADDRESS(1,n,4),1)&":"&LEFT(ADDRESS(1,n+x,4),1)))

which with n=1, x=4 becomes:

COLUMNS(INDIRECT("A"&":"&"E"))

and so:

COLUMNS(A:E)

Answer 5

In the example code below I use variables just to show how the command could be used for other situations.

FirstCol = 1
LastCol = FirstCol + 5
Range(Columns(FirstCol), Columns(LastCol)).Select

Answer 6

no need for loops or such.. try this..

dim startColumnas integer

dim endColumn as integer

startColumn = 7

endColumn = 24

Range(Cells(, startColumn), Cells(, endColumn)).ColumnWidth = 3.8 ' <~~ whatever width you want to set..*