Insert a new column A
Say your last row with data in it is row 777.
Select A1:A777
type this:
=row()
and hit ctrl-enter
This will fill that selected range with that same formula. You should see:
1,2,3, ..., 777.

With A1:A777 still selected, rightclick on the selected area and choose Copy.
Rightclick again and choose Paste Special
Choose Values

Now those formulas should be values.

You can select your range (Say A1:X777) and do your sorting.

Then delete the empty rows.

Then delete column A.



swell estimator wrote:

Dave, thank you for replying. I tired everything I could think of using a
helper column entering =row() in the first cell of the helper column copying
down, tried some other things like puting in the cell reference too but nada.
Pls step me thru it in detail. Precisely what do I put in the 1st cell of
the helper column and the cells below and what do you mean about converting
those formulas to values. thanks, Chris

"Dave Peterson" wrote:

Add an extra helper column.
Fill it with formulas like:
=row()
then convert those formulas to values
Do your sort.
Delete the blank rows if they're not at the bottom.
sort the remaining data by this helper column
delete the helper column.

swell estimator wrote:

Dave, the sort function was the first thing I tried but ascending and
descending compresses the row and removes the original blank rows, but
doesn't keep the original order. Pls look at the following and imagine that
before I manually removed them, each row had 2 blank rows between them.
These are window openings with the width as the first factor and height 2nd
factor
1'(w) x 1'(h) . The 2nd # is the constant and the 1st # the variable in
multiples of ten and they have to remain in the original order in order to be
coherent to someone choosing a window width and height from a list. thanks
for help.

1' x 1' - 8' tall wall, 16" o.c.
2' x 1' - 8' tall wall, 16" o.c.
3' x 1' - 8' tall wall, 16" o.c.
4' x 1' - 8' tall wall, 16" o.c.
5' x 1' - 8' tall wall, 16" o.c.
6' x 1' - 8' tall wall, 16" o.c.
7' x 1' - 8' tall wall, 16" o.c.
8' x 1' - 8' tall wall, 16" o.c.
9' x 1' - 8' tall wall, 16" o.c.
10' x 1' - 8' tall wall, 16" o.c.
1' x 2' - 8' tall wall, 16" o.c.
2' x 2' - 8' tall wall, 16" o.c.
3' x 2' - 8' tall wall, 16" o.c.
4' x 2' - 8' tall wall, 16" o.c.
5' x 2' - 8' tall wall, 16" o.c.
6' x 2' - 8' tall wall, 16" o.c.
7' x 2' - 8' tall wall, 16" o.c.
8' x 2' - 8' tall wall, 16" o.c.
9' x 2' - 8' tall wall, 16" o.c.
10' x 2' - 8' tall wall, 16" o.c.
1' x 3' - 8' tall wall, 16" o.c.
2' x 3' - 8' tall wall, 16" o.c.....
~ ~

"swell estimator" wrote:

I have a large amount of data that is composed of a text row followed by 2
blank rows. This pattern repeats itself in the entire worksheet.

I want to gang remove the blank rows so that I end up with single spaced data.

Any help out there. Thank you.

--

Dave Peterson


--

Dave Peterson