Gord,

You may have seen my other responses in this thread.

I agree that deleting the empty-cell-in-"A" rows is simple, but in
reality, I need to concatenate values between the data
non-empty-cell-in-"A" rows (in other columns) before the delete.

Therefore, the Loop needs to know what and how many cells to
concatenate in each column at each non-empty Col "A" point(s). (Column
A sets the row, but Col B thru Col xx contains the cells to receive the
concatenated value.

Hope I did not confuse you.

Dennis

Gord Dibben wrote:
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