-1

I want to find the last row with text in column A of an excel spreadsheet using a while loop. I'm not sure as to the exact syntax that I need, since I'm new to VBA. I realize there are other ways to find the last row, however I need to use a while loop starting at row 20.

Dim Row As Integer
Row = 20
Do While .Range("A" & Row) <> ""
     Row = Row + 1
Loop
Community
  • 1
  • 1
James Eckhardt
  • 11
  • 1
  • 7
  • 1
    This is a common way to find the last populated row in the column: http://stackoverflow.com/questions/27065840/meaning-of-cells-rows-count-a-endxlup-row – jeff carey Feb 07 '17 at 04:36

2 Answers2

0

Your code will only find the first empty cell in that column, which is not necessarily after the last used row. Why must it be a "While" loop? There are half a dozen ways to find the last used row or column without the while loop. Simply google "vba excel find last row"

John Muggins
  • 1,198
  • 1
  • 6
  • 12
  • I know there are other ways, however I will be using the while loop for other functionality as well, which is why I need it. Even though rows 20-40 in column A are have strings in them, the loop does not progress past 20. Why is this? – James Eckhardt Feb 08 '17 at 01:41
0

Loop to the last row with this:

Dim X as long 'Note, use Long, NOT Integer
For X = 20 to Range("A" & rows.count).end(xlup).row
    'What you want to do in your loop here
Next

Do while is not a good option here as others have pointed out, it will stop when it gets to a blank even if there is data on the cells afterwards.

Personally I try to stay away from naming variables the same as an object or method in VBA.

Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36