Deleting blank rows

[marcopolo]

I have a Crystal report imported into excel that contains numerous "blank" rows and it is making my reporting increasingly difficult as my report has now grown to 98 pages. Is there a way to delete all blank rows from a sheet?

thanks

 

[VoG]

Try running this macro

Sub DelBlanks()
On Error Resume Next
Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

 

[JackDanIce]

Welcome to the board

You could filter for all the blank rows and then run this macro which will delete every filtered row except the header:

Sub DeleteVisibleRows()
With Application
    .DisplayAlerts = False
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
ActiveSheet.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
ActiveSheet.AutoFilterMode = False
With Application
    .DisplayAlerts = True
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

A modification to this would be for the macro to actually filter the data for you and then delete those rows but this is what I currently have stored as a button as I'm always changing what filters I'm deleting so works as a quick-answer for me

 

[marcopolo]

Thanks VoG.....macro worked great! you just saved me a ton of time.