remove blank rows

my excel has 20,000 rows. some of them are blank. how can i
a) copy the non-blank rows in a different sheet
b) delete the blank rows from the original sheet

to get around problem (b) if i filter on non-blank rows and try to
delete them, i get an error "The selection is too complex...."

Can you sort by a column that is only empty when the row is empty?

If you need to put the data back in it's original order, use a helper column
directly to the right of your data.

Put =row() in row one of that column
drag it down the column.
Select that column and
edit|copy
edit|paste special|values

Sort your data based on that column that has info on it. Delete the blanks
rows. Resort the data by this helper column. Delete the helper column.

vernalGreens@gmail.com wrote:
>
> my excel has 20,000 rows. some of them are blank. how can i
> a) copy the non-blank rows in a different sheet
> b) delete the blank rows from the original sheet
>
> to get around problem (b) if i filter on non-blank rows and try to
> delete them, i get an error "The selection is too complex...."

--

Dave Peterson

Hi vernalGreens,
The technique of creating a column of original row numbers as constants , sorting
to place rows with blank cells in a particular column to the bottom, removing the
group of empty rows, and resorting on original row numbers
will work and it is one to always remember, but the use of generic macros to do such things
is a lot faster, especially since you will probably be doing this on a regular bases.

Variations to delete rows based on an empty cell in one column
http://www.mvps.org/dmcritchie/excel/delempty.htm#rows
or a complete check of all columns in a row to determine if row is empty.
http://www.mvps.org/dmcritchie/excel...emoveEmptyRows)

BTW, if you were going to use the fill handle to drag down through 20,000 rows
it would take awhile to get down to where you want without speeding past row 20,000.
You would probably be better off using the address box with G1:G20000
and the formula in G1 then use shortcut Ctrl+D (not the fill handle).
More information on use of the fill handle and use of related shortcuts.
http://www.mvps.org/dmcritchie/excel/fillhand.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message news:43E6B757.8A1A758A@verizonXSPAM.net...
> Can you sort by a column that is only empty when the row is empty?
>
> If you need to put the data back in it's original order, use a helper column
> directly to the right of your data.
>
> Put =row() in row one of that column
> drag it down the column.
> Select that column and
> edit|copy
> edit|paste special|values
>
> Sort your data based on that column that has info on it. Delete the blanks
> rows. Resort the data by this helper column. Delete the helper column.
>
> vernalGreens@gmail.com wrote:
> >
> > my excel has 20,000 rows. some of them are blank. how can i
> > a) copy the non-blank rows in a different sheet
> > b) delete the blank rows from the original sheet
> >
> > to get around problem (b) if i filter on non-blank rows and try to
> > delete them, i get an error "The selection is too complex...."
>
> --
>
> Dave Peterson

I use these variations of macros to delete empty rows all the time.
Hope it helps

http://www.ozgrid.com/VBA/VBACode.htm

Jacob