Do Loop to delete empty rows

Hi Guys, I have a downloaded report in Excel which is 32 pages long and full of page breaks. I would like a Macro Do Loop which deletes all the blank lines. Can someone help?
Example3.xlsx
JustinFinancial ControlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

Sub DeleteRows()
If WorksheetFunction.CountBlank(Range("1:1")) = Range("1:1").Cells.Count Then Range("1:1").EntireRow.Delete
    With ActiveSheet
        .AutoFilterMode = False 'remove filter
        With .Range("A:G")
            .AutoFilter Field:=1, Criteria1:="="
            .AutoFilter Field:=2, Criteria1:="="
            On Error Resume Next ' for the case when there is no visible rows
            .Resize(.Rows.Count - 1).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
            On Error GoTo 0
        End With
        .AutoFilterMode = False 'remove filter
    End With
End Sub

Open in new window

Regards
Professor JMicrosoft Excel ExpertCommented:
this code below will delete all blank rows

Sub DeleteRows()
ActiveSheet.UsedRange.Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'Turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'Work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Open in new window

Rob HensonFinance AnalystCommented:
Select the whole area and apply an AutoFilter. Select a column that you know will be empty. Select Blanks.

Highlight the whole area and Delete Rows. It will only delete those that are visible.

Thanks
Rob H
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

JustinFinancial ControlAuthor Commented:
Hi RGonzo, your Macro looks good, but I want to take out the headings of each page as they are just repeating. Can you adapt the Macro to delete the rows where column 11 is blank?
JustinFinancial ControlAuthor Commented:
Hi RGonzo, your Macro looks good, but I want to take out the headings of each page as they are just repeating. Can you adapt the Macro to delete the rows where column 11 is blank?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Professor JMicrosoft Excel ExpertCommented:
simply add this line either in my code above like this or in Rgonzo's code

Columns("K").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Open in new window


Sub DeleteRows()
ActiveSheet.UsedRange.Select
'Deletes the entire row within the selection if the ENTIRE row contains no data.
Dim i As Long
'Turn off calculation and screenupdating to speed up the macro.
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
'Work backwards because we are deleting rows.
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i
Columns("K").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

Open in new window

JustinFinancial ControlAuthor Commented:
It works great
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.