You have a good point!

I tried to keep my need simple.

The worksheets were prepared by new users who chose to enter text under
a Column heading, in many rows vs. just one cell using Alt-Enter for
paragraphing in the cell.

So, before I delete the extra cell-rows, I must concatenate the
multiple-cell information into the cell where there is information in
Column "A".

If this can be done by going in reverse fine! I just did not think of
it.

Thanks

Maistrye wrote:
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

I may be looking at this wrong, but wouldn't it be better to loop
starting at the 1000th row and stepping back through to row 1? Then,
just delete each row that doesn't have a value in column A and proceed
to the next row. If you go from 1 to 1000, it seems to me there are
more complications, which is why it seems that going backwards would be
better.

Scott


--
Maistrye
------------------------------------------------------------------------
Maistrye's Profile: http://www.excelforum.com/member.php...o&userid=36078
View this thread: http://www.excelforum.com/showthread...hreadid=563416