0

I am trying to set up a simple for loop in Excel VBA that starts at a specified row, and iterates till the end of the document? I cant seem to find out how to do this through google. Any help would be greatly appreciated.

Scalahansolo
  • 2,615
  • 6
  • 26
  • 43

3 Answers3

1

This will loop through rows until a blank cell is found. 

  Dim iRow As Integer
  iRow = 0
  Do
    iRow = iRow + 1
    'do something
    Debug.Print ActiveSheet.Range("A" & iRow).Value

  Loop Until ActiveSheet.Range("A" & iRow).Formula = ""
nemmy
  • 753
  • 1
  • 6
  • 19
  • Note that this won't work if you have any blank rows in the range you are looping through. – Jon Crowell Jun 03 '13 at 12:33
  • Yes, that's why it says "will look through rows until a BLANK cell is found". – nemmy Jun 03 '13 at 21:02
  • 1
    Many data sets have blank rows sprinkled throughout. If you stop once you hit a blank, you won't process all of the data. The standard approach to this is to find the last row and them stop when you hit that, NOT a blank. – Jon Crowell Jun 03 '13 at 21:26
1

If you would like the user to specify which cell to start in this will work.

Sub myLoop()
Dim userInput As String
Dim count As Integer
Dim first As Integer

userInput = InputBox("Which cell would you like to start in?", "Enter Range like 'A1'")
first = ActiveSheet.Range(userInput).Row
count = ActiveSheet.UsedRange.Rows.count

For i = first To count
    MsgBox "Row: " & i
    'Replace the above msgbox function with the code you would like to run
Next i
End Sub
Ryan E
  • 499
  • 1
  • 7
  • 17
-2
Dim CurrentRow
Dim LastRow
Dim i

CurrentRow = ActiveCell.Row
CurrentColumn = ActiveCell.Column
LastRow = ActiveSheet.Rows.Count

For i = CurrentRow to LastRow
   Cells(i, CurrentColumn).Value = "X"
Next i
marlenunez
  • 626
  • 4
  • 9