fifty thousand rows

[gearhead]

My Excel 2007 spreadsheet has more than fifty thousand rows. Five out
of every six rows are blank, and I have to delete the blank rows. I
found instructions on the web how to do this, but the technique
doesn't work on big documents. I used find/select > go to special >
blanks > delete > delete sheet rows, whereupon excel deleted about the
first fifty blank rows and left the rest of the document untouched.
I tried to repeat the operation, but after the first go-round I just
got glitches -- it would either delete data or didn't delete anything.
The other option I've considered is turning my spreadsheet into a
comma-separated text file, opening it up in Word, do a find/replace
that replaces any series of multiple commas with a single comma, then
load back into Excel. But I'm running up against a roadblock on that
too. I saved my doc in excel as a csv file, but I can't open it in
Word.
Please help. I have a deadline.

 

[Jim Cone]

Hello, again...
The cells are not blank by Excel's definition, so...
Add an index column - consecutively numbered.
Sort ALL of the data by the "blank" column (include the index column)
Delete the "blank rows" - they will all be grouped together.
Sort again by the index column
Delete the index column
--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(Permutations: with valid words highlighted)



"gearhead" <[email protected]>
wrote in message
news:480197b6-63c9-494b-9b26-49b8a73d9ac1@s18g2000vbe.googlegroups.com...

 

[gearhead]

Jim,
Okay, I get the concept and I'm going to try it. I might have some
more questions later. Thanks.
Mike Robinson

 

[gearhead]

I auto-filled a column next to my data. Now each cell that contains a
value sits next to an integer that's a multiple of six, and the blank
cells sit next to all the numbers that are not multiples of six.
If I divide my index column by six, I wll get a mix of integers and
non-integers. Then I can eliminate all the rows where my index is not
an integer. That will get rid of the blanks and keep the data
entries. Is there a way I can do that with a simple filter? Or do I
have to use a macro?
Mike

 

[Jim Cone]

You should be doing all this on a copy of your data.
Hold the Ctrl key down and drag the data sheet to the right (pass the next tab) and let
go.
Do that as many times as needed. You are now free to experiment.

What happened to the sort suggestion?
I can't think of a way to filter on integers vs. non integers, but I am still learning
after 15 years or so.
You could run a mod formula on the indexes and return a zero and filter on it.
But you could have already sorted the data and been done.
'---
Jim Cone


"gearhead" <[email protected]>
wrote in message
I auto-filled a column next to my data. Now each cell that contains a
value sits next to an integer that's a multiple of six, and the blank
cells sit next to all the numbers that are not multiples of six.
If I divide my index column by six, I wll get a mix of integers and
non-integers. Then I can eliminate all the rows where my index is not
an integer. That will get rid of the blanks and keep the data
entries. Is there a way I can do that with a simple filter? Or do I
have to use a macro?
Mike

 

[Jim Cone]

That should be: ...drag the data sheet tab to the right...



"Jim Cone" <[email protected]>
wrote in message
news:[email protected]...