Macro that will loop through all pages and delete blank rows in the table on each page

[Twollaston]

I have a workbook with 300 pages, each page has 1 excel table, a lot of the tables have blank rows somewhere in the table. Does anyone have a macro that can loop through all the sheets and delete the blank rows in the tables? All the tables start in A1 of every sheet.

 

[mumps]

Try:

Sub DelBlankRows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Twollaston]

Try:

Sub DelBlankRows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Next ws
    Application.ScreenUpdating = True
End Sub

Thanks for providing that, I appreciate it.

It gave me a "Runtime error 1004: Delete method of range class failed" and it referenced this line
ws.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete

 

[mumps]

Are all the cells in each "blank" row actually blank?
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[Joe4]

mumps,

Note that your code works fine on straight lists of table, but not if they are actual Excel "tables" (and the OP did specifically mention "tables" multiple times in their post).
Tables produce the error message they mentioned.

 

[mumps]

Thank you @Joe4.

Maybe this:

Sub DelBlankRows()
    On Error Resume Next
    With Range("Table1")
        Intersect(.Cells, .SpecialCells(xlCellTypeBlanks).EntireRow).Delete
    End With
End Sub

 

[Twollaston]

Are all the cells in each "blank" row actually blank?
It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

Thank you I was able to get your original solution to work by running a macro first to convert everything to a normal range

Solution I used was to run this macro first:

Sub MakeAllTablesRegularRanges()
  Dim WS As Worksheet, LO As ListObject
  For Each WS In Worksheets
    For Each LO In WS.ListObjects
      LO.Unlist
    Next
  Next
End Sub

And then to run this macro:

Sub DelBlankRows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        ws.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    Next ws
    Application.ScreenUpdating = True
End Sub

Thanks for your help mumps

 

[mumps]

Try this macro without converting to a range:

Sub DelBlankRows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        With ws.ListObjects(1)
            For i = .ListRows.Count To 1 Step -1
                If .ListRows(i).Range.Cells(1) = "" Then
                    .ListRows(i).Delete
                End If
            Next i
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

 

[Twollaston]

Try this macro without converting to a range:

Sub DelBlankRows()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    For Each ws In Sheets
        With ws.ListObjects(1)
            For i = .ListRows.Count To 1 Step -1
                If .ListRows(i).Range.Cells(1) = "" Then
                    .ListRows(i).Delete
                End If
            Next i
        End With
    Next ws
    Application.ScreenUpdating = True
End Sub

Thank you Mumps! this one works perfectly!

 

[mumps]

You are very welcome.