May be easier to just find the blank rows and delete them.

Public Sub DeleteRowOnCell()
''delete any row that has a blank in selected column(s)
Set coltocheck = Application.InputBox(prompt:= _
"Select A Column", Type:=8)
coltocheck.SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
ActiveSheet.UsedRange
End Sub


Gord Dibben MS Excel MVP


On 20 Jul 2006 10:44:23 -0700, "Dennis" wrote:

Using 2003


Assume that Column A has a UsedRange(Rows) of 1000 cells.

Of those cells, only 22 are data-filled.

What is the best way in VBA to compute the Address and Row Number of
the first cell in "A" that has a value? (Assume "A7")

What is the best way in VBA to compute the Address and Row Number of
the Second cell in "A" that has a value? (Assume "A40")

What is the best way in VBA to compute the Address and Row Number of
the (Other cells in "A" that have values? etc.


Once I have the above then I know that Cell A8 through Cell A39 are
blank which rows I would like to delete.

The challenge is there are about 21 other blank-cell ranges in the
UsedRange.

I would like a VBA Loop to delete each of the blank ranges:
The Loop needs to know the Address to start and the number of rows
to delete;
Which means I also need to reset the loop's counter variable with
the number of rows [like A39 - A7 +1 equals 34] each time a group of
rows are deleted.

What is the smartest way to:
1) start the loop just after value 1 in Column A and
2) "process" through to Value 2 in Column A Then
3) reset the Loop Variables so that the loop starts at Value 2 in
Column A and proceeds to Value 3 in Column A, etc, etc?

Thanks

Dennis