For loop through a row

user_11535108 0
Viewed 42

I have a worksheet with entries in column A and row 1:

enter image description here

It is possible with the following code to run a For-Loop through any amount of entries in column A:

For i = 1 To Worksheets("Sheet1").Range("A1").End(xlDown).Row

    '' Do whatever

Next i

But it is not possible to run a For-Loop through any amount of entries in row 1 with the code below and my question is why?

For i = 1 to Worksheets("Sheet1").Cells(1).End(xlRight).Column

    '' Do whatever

Next i
vba excel
2 Answers

Because it is xlToRight not xlRight

Anyway I recommend to use

Worksheets("Sheet1").Cells(1, Worksheets("Sheet1").Columns.Count).End(xlToLeft).Column

and

Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, "A").End(xlUp).Row

to find the last used column/row, because it is reliable even if there are blanks.

user_32196130

The xlRight constant is not the correct one to use it in such a code. The correct one should be xlToRight.

But using this way is not the best one, if there is an empty cell in the row to be processed.

That's why, a better solution should be the next:

Sub testIterateThroughColumns()
    Dim sh As Worksheet, i As Long
    
    Set sh = Worksheets("Sheet1")
    For i = 1 To sh.cells(1, sh.Columns.count).End(xlToLeft).row
        'do what you need...
    Next i
End Sub
user_22333080
Related