CODEWITHSUNDEEP
vbaexcel
wahyueka31
wahyueka31

Reputation: 672

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?

Upvotes: 23

Views: 279539

Answers (4)

user14543082
user14543082

Reputation: 1

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

Upvotes: 0

bonCodigo
bonCodigo

Reputation: 14361

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

Upvotes: 11

Larry
Larry

Reputation: 2794

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

Upvotes: 36

whytheq
whytheq

Reputation: 35557

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

Upvotes: 4

Related Questions