Excep Macro: delete blank rows

I don’t know if you have ever had an excel file that was way larger then it should be based on the content to had in it. Well i deal with this quite a bit and it’s annoying to say the least but i have a little code that will clear it right up.

Sub DeleteBlankRows()
    Dim Rw As Long, RwCnt As Long, Rng As Range 

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual 

On Error Goto Exits: 

    If Selection.Rows.Count > 1 Then
        Set Rng = Selection
    Else
        Set Rng = Range(Rows(1), Rows(ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row()))
    End If
    RwCnt = 0
    For Rw = Rng.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(Rng.Rows(Rw).EntireRow) = 0 Then
            Rng.Rows(Rw).EntireRow.Delete
            RwCnt = RwCnt + 1
        End If
    Next Rw 

Exits:
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic 

End Sub

Using this script allows you to delete any blank rows from a spreed sheet and could bring down your file size by a great measure.

I also used this code as an Addin, the deleteblankrows_newer is for office newer than 2003 and deleteblankrows_older is for office 2003 and older.

deleteblankrows_newer

deleteblankrows_older

I hope this helps you out my fellow code monkeys and please comment and let me know how you like it!

P.S. if you don’t know how to add a macro to excel comment below and i will make a post about it.

Fedora Nerd

Leave a Reply

Your email address will not be published. Required fields are marked *