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   amp  i  amp    D   amp  i  Select    i   i   1 Loop Until i  gt  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

User · Answer

Just use the Cells function and loop thru columns  Cells Row Column

User · Answer

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

User · Answer

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  gt  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  gt  10    End Function

User · Answer

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  gt  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

[vba] Excel VBA Loop on columns

Examples related to vba

Examples related to excel