gang remove blank rows

  • Thread starter Thread starter swell estimator
  • Start date Start date
S

swell estimator

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.
 
Can you just sort your data?

swell said:
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.
Click to expand...
 
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.....
~ ~
 
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.
 
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
 
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.
 
Dave , the () in =row() is a double paren correct?

Here's how I set up a trial of this that I could not get to work. I used a
portion of my data and copied it into column A using 208 rows, 1/3 of which
was the text formerly listed with 2/3's of the rows blank. I then inserted a
new column to the left of my column and that became column A and my original
now column B.

I selected all 208 cells in column A only, typed =row() in the top cell and
hit ctrl,enter and the column filled with #'s from 1 to 208. While column
A1-A208was selected I right-clicked on copy and again right clicked on paste
special. I then selected both columns A and B to rows 1 - 208, accessed Data
and clicked on Sort. The dialog button gave me the choice of column A or B.
Column A didn't do anything but selecting column B compressed B1- B208 in the
same way as previously when I just hit ascending on the tool bar. bers were
not in the correct order. They were in this order:

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

What did I do wrong?
 
Dave , the () in =row() is a double paren correct?

Here's how I set up a trial of this that I could not get to work. I used a
portion of my data and copied it into column A using 208 rows, 1/3 of which
was the text formerly listed with 2/3's of the rows blank. I then inserted a
new column to the left of my column and that became column A and my original
now column B.

I selected all 208 cells in column A only, typed =row() in the top cell and
hit ctrl,enter and the column filled with #'s from 1 to 208. While column
A1-A208was selected I right-clicked on copy and again right clicked on paste
special. I then selected both columns A and B to rows 1 - 208, accessed Data
and clicked on Sort. The dialog button gave me the choice of column A or B.
Column A didn't do anything but selecting column B compressed B1- B208 in the
same way as previously when I just hit ascending on the tool bar. bers were
not in the correct order. They were in this order:

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

What did I do wrong?
 
Use Data>Filter. Select Blanks. Now only blank rows will be displayed.
Select these rows, right-click on any row, select Delete Row.

Regards,
Fred.
 
Hi,

You can do this just using the first column.
Select all of your first column,
Tap F5
Click on Special
Check Blanks
Click OK
Then right click on the selected Blanks
Click Delete
And check Entire rows

HTH
Martin
 
Yep. =row() is the correct formula.

Sort your data to get put the empty rows together.
then delete those empty rows
Sort your data another time by column A.
delete column A

swell said:
Dave , the () in =row() is a double paren correct?

Here's how I set up a trial of this that I could not get to work. I used a
portion of my data and copied it into column A using 208 rows, 1/3 of which
was the text formerly listed with 2/3's of the rows blank. I then inserted a
new column to the left of my column and that became column A and my original
now column B.

I selected all 208 cells in column A only, typed =row() in the top cell and
hit ctrl,enter and the column filled with #'s from 1 to 208. While column
A1-A208was selected I right-clicked on copy and again right clicked on paste
special. I then selected both columns A and B to rows 1 - 208, accessed Data
and clicked on Sort. The dialog button gave me the choice of column A or B.
Column A didn't do anything but selecting column B compressed B1- B208 in the
same way as previously when I just hit ascending on the tool bar. bers were
not in the correct order. They were in this order:

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

What did I do wrong?
Click to expand...
 
I want to thank Dave especially for his patience and diligence for staying
with my problem til I was given a solution.

And thank you very much Martin - your solution was the simplest and it
worked immediately leaving my data in the correct order as I needed.

And thank you Fred. Perhaps because I'm using Excel 2003, filtering the
data worked this way. After clicking filter on the Data menu, 2 choices:
Advanced and Autofilter. Advanced didn't give me options that I could see
but Autofilter allowed me to select blanks and non-blanks. I first selected
blanks and the whole column went blank. When I right clicked and selected
delete or delete row, the whole column collapsed with delete - and nothing
happened with delete-row.

But by going back in to Autofilter and selecting Non-blanks I achieved the
results I wanted - all the items in the order. I assumed the blank rows
below were THE blank rows but it didn't seem necessary to delete them. I
tried both ways.

Could Excel 2003 possibly have a different way of using the Data filter or
did I just not know what I was doing?

Thank you guys for saving me a ton of tedious work that would have been
required to manually remove the blank rows. Chris, the swell estimator
 
Hi Chris,

Glad to hear you got it working.

With Fred's method you seem to have missed the part where he said "select
these rows". After you have applied the Autofilter, select all of the blank
rows by their row headers (that selects the entire rows), right click on one
of the row headers
and select delete, then go back to Data>Filter>Autofilter and click on it to
turn it off.

When you have time to play around you may also want to persist with Dave's
method to work out how that works. In Excel, as with virtually all programs,
there are a lot of different ways of achieving the same result, all useful in
different situations. The more you have in your armoury, the better equiped
you are whan those tricky little situations arise.

Cheers
Martin
 
Back
Top