CODEWITHSUNDEEP
vbaexcel
gemmo
gemmo

Reputation: 1456

VBA - Select columns using numbers?

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

Upvotes: 23

Views: 219709

Answers (9)

dapaz
dapaz

Reputation: 813

I was looking for a similar thing. My problem was to find the last column based on row 5 and then select 3 columns before including the last column. 

Dim lColumn As Long

lColumn = ActiveSheet.Cells(5,Columns.Count).End(xlToLeft).Column
MsgBox ("The last used column is: " & lColumn)
Range(Columns(lColumn - 3), Columns(lColumn)).Select

Message box is optional as it is more of a control check. If you want to select the columns after the last column then you simply reverse the range selection

Dim lColumn As Long

lColumn = ActiveSheet.Cells(5,Columns.Count).End(xlToLeft).Column
MsgBox ("The last used column is: " & lColumn)
Range(Columns(lColumn), Columns(lColumn + 3)).Select

Upvotes: 0

McPaddy
McPaddy

Reputation: 234

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

Upvotes: 14

Burak Borhan
Burak Borhan

Reputation: 21

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..*

Upvotes: 2

Dave F
Dave F

Reputation: 161

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

Upvotes: 2

Edy_Tang
Edy_Tang

Reputation: 1

In this way, you can start to select data even behind column "Z" and select a lot of columns.

Sub SelectColumNums()
    Dim xCol1 As Integer, xNumOfCols as integer
    xCol1 = 26
    xNumOfCols = 17
    Range(Columns(xCol1), Columns(xCol1 + xNumOfCols)).Select
End Sub

Upvotes: -1

L42
L42

Reputation: 19727

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.

Upvotes: 31

Nam Taf
Nam Taf

Reputation: 64

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)

Upvotes: 1

SeanC
SeanC

Reputation: 15923

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

Upvotes: 4

Pieter Geerkens
Pieter Geerkens

Reputation: 11883

You can specify addresses as "R1C2" instead of "B2". Under File -> Options -> Formuals -> Workingg with Formulas there is a toggle R1C1 reference style. which can be set, as illustrated below.

enter image description here

Upvotes: 0

Related Questions