Do Loop to delete empty rows

Justin
Justin used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
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 Expert
Top Expert 2014
Commented:
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 Analyst

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

JustinFinancial Control

Author

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?
Financial Control
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?
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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 Control

Author

Commented:
It works great

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial