Deleting rows of blank data

  • Thread starter Thread starter Ken
  • Start date Start date
K

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
 
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
 
I thank everyone for there help... I just need help on how to set this macro
up.

Thank You again
 
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.
 
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.
 
Back
Top