Excel VBA Loop on columns

Question

when we are going to do a loop in the rows, we can use code like the following:

i = 1
Do
   Range("E" & i & ":D" & i).Select
   i = i + 1
Loop Until i > 10

but what if we want to do a loop on a column?

Can we use the same method as above?

while the columns in Excel is a complex such as A, B, C, ..., Y, Z, AA, AB, AC, ..., etc. problems will arise between loop from the "Z" to the "AA".

how we do looping alphabet column from "A" to "Z" and then continued into "AA", "AB" and so on

is there anything that can help?

Larry · Accepted Answer · 2012-12-21 10:53:41Z

36

Yes, let's use Select as an example

sample code: Columns("A").select

How to loop through Columns:

Method 1: (You can use index to replace the Excel Address)

For i = 1 to 100
    Columns(i).Select
next i

Method 2: (Using the address)

For i = 1 To 100
 Columns(Columns(i).Address).Select
Next i

EDIT: Strip the Column for OP

columnString = Replace(Split(Columns(27).Address, ":")(0), "$", "")

e.g. you want to get the 27th Column --> AA, you can get it this way

edited Dec 21, 2012 at 10:53

answered Dec 21, 2012 at 6:31

Larry

2,7922 gold badges27 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Siddharth Rout Over a year ago

+ 1 for giving both methods... :) Though 2nd one becomes useless when u have method 1.

wahyueka31 Over a year ago

do you know how we can get the column index with loop as string? like "A", "B", ..., "AA", "AB", ..., etc. so I can apply it to my loop like this : Range(colIndex & i & ":" & colIndex & i).Select "colIndex" is a variable to loop column index in excel

bonCodigo Over a year ago

@Larry it's best to ask why he needs the column name :) I am curious

whytheq Over a year ago

@Rebel generally there's never a need to use the column letters; this is just how the Excel UI is designed but under the hood the columns are indexed like any other collection ... using numbers. So unless really necessary use numbers.

BenMorel · Accepted Answer · 2014-01-29 17:47:07Z

11

Another method to try out. Also select could be replaced when you set the initial column into a Range object. Performance wise it helps.

Dim rng as Range

Set rng = WorkSheets(1).Range("A1") '-- you may change the sheet name according to yours.

'-- here is your loop
i = 1
Do
   '-- do something: e.g. show the address of the column that you are currently in
   Msgbox rng.offset(0,i).Address 
   i = i + 1
Loop Until i > 10

** Two methods to get the column name using column number**

Split()

code

colName = Split(Range.Offset(0,i).Address, "$")(1)

String manipulation:

code

Function myColName(colNum as Long) as String
    myColName = Left(Range(0, colNum).Address(False, False), _ 
    1 - (colNum > 10)) 
End Function

edited Jan 29, 2014 at 17:47

BenMorel

37k52 gold badges208 silver badges339 bronze badges

answered Dec 21, 2012 at 8:40

bonCodigo

14.4k1 gold badge52 silver badges93 bronze badges

3 Comments

wahyueka31 Over a year ago

do you know how we can get the column index with loop as string? like "A", "B", ..., "AA", "AB", ..., etc. so I can apply it to my loop like this : Range(colIndex & i & ":" & colIndex & i).Select "colIndex" is a variable to loop column index in excel

bonCodigo Over a year ago

@Rebel It is possible. But out of curiosity, since there are many effective ways to loop through the columns, why do you want to get the String column name?

bonCodigo Over a year ago

@Rebel I have put two different methods to find the column name. Please share with us, why you need it :D

whytheq · Accepted Answer · 2012-12-21 10:41:19Z

4

If you want to stick with the same sort of loop then this will work:

Option Explicit

Sub selectColumns()

Dim topSelection As Integer
Dim endSelection As Integer
topSelection = 2
endSelection = 10

Dim columnSelected As Integer
columnSelected = 1
Do
   With Excel.ThisWorkbook.ActiveSheet
        .Range(.Cells(columnSelected, columnSelected), .Cells(endSelection, columnSelected)).Select
   End With
   columnSelected = columnSelected + 1
Loop Until columnSelected > 10

End Sub

EDIT

If in reality you just want to loop through every cell in an area of the spreadsheet then use something like this:

Sub loopThroughCells()

'=============
'this is the starting point
Dim rwMin As Integer
Dim colMin As Integer
rwMin = 2
colMin = 2
'=============

'=============
'this is the ending point
Dim rwMax As Integer
Dim colMax As Integer
rwMax = 10
colMax = 5
'=============

'=============
'iterator
Dim rwIndex As Integer
Dim colIndex As Integer
'=============

For rwIndex = rwMin To rwMax
        For colIndex = colMin To colMax
            Cells(rwIndex, colIndex).Select
        Next colIndex
Next rwIndex

End Sub

edited Dec 21, 2012 at 10:41

answered Dec 21, 2012 at 8:04

whytheq

35.7k69 gold badges179 silver badges282 bronze badges

2 Comments

wahyueka31 Over a year ago

do you know how we can get the column index with loop as string? like "A", "B", ..., "AA", "AB", ..., etc. so I can apply it to my loop like this : Range(colIndex & i & ":" & colIndex & i).Select "colIndex" is a variable to loop column index in excel

whytheq Over a year ago

@Rebel that is the same as .Range(.Cells(i, colIndex), .Cells(i, colIndex)).Select ....which is what I put in my suggested answer just with more desciptive names. BUT in your comment you mentioned Range(colIndex & i & ":" & colIndex & i).Select ....no need for the repeated section after the colon: Range(colIndex & i).Select ....which is actually just equivalent to .cells(i,colIndex)

user14543082 · Accepted Answer · 2020-10-29 14:19:56Z

0

Just use the Cells function and loop thru columns. Cells(Row,Column)

answered Oct 29, 2020 at 14:19

user14543082

1

Collectives™ on Stack Overflow

Excel VBA Loop on columns

4 Answers 4

4 Comments

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related