[vba] Excel VBA Loop on columns

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?

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

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

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