Deleting rows of blank data

[Ken]

I have imported a report into Excel and it consists of 79 pages. The problem
is there are large gaps of cells between the data. I believe if I could
delete the blank rows between my data I could shrink it down to probably 20
pages..


If anyone knows of a way to do delete these rows quickly or make my data
shrink into less pages that would be of great help.


Thank You

 

[Frank Kabel]

Hi Ken
have a look at
http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows

 

[Ken]

How can I set this up in Macros

Thank You for your help with this

 

[Bernie Deitrick]

Ken,

If you can do your deletion based on a single column, then select that
entire column and then run this:

Sub DeleteBlankRows()
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

or change the code to always look at one column, like column A:

Sub DeleteBlankRows()
Range("A:A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

HTH,
Bernie
MS Excel MVP

 

[Ken]

I thank everyone for there help... I just need help on how to set this macro
up.

Thank You again

 

[Bernie Deitrick]

Ken,

Visit this for a quick start on macro programming:
http://www.mvps.org/dmcritchie/excel/install.htm#install

HTH,
Bernie
MS Excel MVP

 

[Tim Zych]

Press ALT + F11.

Paste in the macro supplied:

Sub DeleteBlankRows()
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

To run it, go back to the sheet, select the applicable column, select
Tools -> Macro -> Macros -> select the procedure you pasted and press Run.

To do the same thing without macros, select the column, go to Edit ->
Goto -> Special... -> Blanks -> OK -> Edit -> Delete (shift cells up) -> OK.

 

[Tim Zych]

Forgot.
After pressing ALT + F11, insert a module via Insert menu -> Module. Then do
the rest.

Here is the complete snippet for completion.

Press ALT + F11. Insert a regular module via Insert -> Module.

Paste in the macro supplied:
Sub DeleteBlankRows()
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

To run it, go back to the sheet, select the applicable column, select
Tools -> Macro -> Macros -> select the procedure you pasted and press Run.

To do the same thing without macros, select the column, go to Edit ->
Goto -> Special... -> Blanks -> OK -> Edit -> Delete (shift cells up) -> OK.