loop until it hits empty cell

[Bukol]

Here we go again:

I have a list of data in column A and want to do some action using the data in cell A1, then delete row 1 so the data previously in A2 is now in A1. Next loop.

The problem is, I don't know in advance how many rows have data in it. So I need to create a loop: "For I = 1 to (until cell A1 is empty)"
How to do??
Hans

[brettvba]

On 2002-04-21 12:07, Bukol wrote:
Here we go again:

I have a list of data in column A and want to do some action using the data in cell A1, then delete row 1 so the data previously in A2 is now in A1. Next loop.

The problem is, I don't know in advance how many rows have data in it. So I need to create a loop: "For I = 1 to (until cell A1 is empty)"
How to do??
Hans

how about
do until activecell = ""

[Tom Schreiner]

Hi Bukol.
This will give you the last row used which may be the number you need...

LastRow = Range("A1:A" & Range("A65536").End(xlUp).Row).Rows.Count

Tom

[jrnyman]

Here's a few more options to consider as I've been doing a similar routine a lot lately:

(1) instead of starting in A2 and going down, start at the last row and work up. This way deleting the row doesn't mess up your counter

(2) If you have to work down, use a Goto command at the end of the loop that send it to the line just below the For command, so it repeats the loop for the same row it was in, which now has new info due to the previous delete. Just make sure to use an If statement to get out of the loop

(3) Instead of deleting rows, consider just clearing them with ActiveCell.EntireRow.clear
You can then delete blank rows later, or just continue working

[Bukol]

Thanks guys,
I didn't reply yet because I am still working the options.
Will let you know.
Hans