Your problem is finding the last row of data. Use this statement

Lastrow = cells(rows.count,"A").end(xlup).Row

row.count is an excel constant which is 65536.
end(xlup) move from 65536 to the first non empty cell

You can then modify your loop

for i = 2 to LastRow

next i

"Charles Chickering" wrote:

Pman, if you're trying to leave only one blank row in at a time try something
like this:
Dim cnt as Long
Dim MyCell as Range
For cnt = 503 to 2 Step -1
Set MyCell = Range("B" & cnt)
If MyCell = "" and MyCell.Offset(-1) = "" Then MyCell.EntireRow.Delete
Next

--
Charles Chickering

"A good example is twice the value of good advice."


"Pman" wrote:

Hi,

I have a data sheet in which I have written a macro to add up columns upto
row #503. However every month I do not need 503 rows, sometimes its as small
as 20, or sometimes I need all the 503 rows. I was wondering if anyone could
help me figure out as to how to delete rows if more than three consecutive
rows are blank (this is because there is a blank row in between every unique
entity in a cell).
For eg: column "B" has data as follows:
1
1
1

2
2

3

4
4
4








TOTAL

If I use a macro to delete rows, it deletes the rows between the unique
values as well....hence I thought if a macro can be written that can delete
the blank rows till my total.

Thanks in Advance.....and Thanks to everyone who has helped me in the past :)

P