Delete table row if entire row is empty

[Moluccanmom]

Hello,

I am looking for a macro that deletes table rows that are completely empty.



Thank you,
Moluccanmom

 

[SteveO59L]

try posting a sample of your data, and what you would like to do with it

 

[My Aswer Is This]

Assuming your table is on the active sheet.
And assuming your table is named: MyTable
Modify script to change Table name
Try this:

Sub Check_Table()
'Modified 4-19-18 5:35 AM EDT
Application.ScreenUpdating = False
Dim ans As Long
ans = ActiveSheet.ListObjects("MyTable").DataBodyRange.Rows.Count
With ActiveSheet.ListObjects("MyTable").DataBodyRange
    For i = ans To 1 Step -1
        ans = Application.WorksheetFunction.CountA(.Rows(i))
            If ans = 0 Then ActiveSheet.ListObjects("MyTable").DataBodyRange.Rows(i).Delete
        Next
End With
Application.ScreenUpdating = True
End Sub

 

[Moluccanmom]

Thank you, you are correct, my table is on the active sheet.
I pasted your code in a module and changed the table name in the code,
but my empty rows are still there, do you have an idea what I could be doing wrong?
Thanks!

 

[My Aswer Is This]

I test all my scripts:

Show me your modified script.

 

[Moluccanmom]

Sub Check_Table()'Modified 4-19-18 5:35 AM EDT
Application.ScreenUpdating = False
Dim ans As Long
ans = ActiveSheet.ListObjects("Data").DataBodyRange.Rows.count
With ActiveSheet.ListObjects("Data").DataBodyRange
    For i = ans To 1 Step -1
        ans = Application.WorksheetFunction.CountA(.Rows(i))
            If ans = 0 Then ActiveSheet.ListObjects("Data").DataBodyRange.Rows(i).Delete
        Next
End With
Application.ScreenUpdating = True
End Sub

 

[Moluccanmom]

My table is on the active sheet, A1:S877

 

[Peter_SSs]

See if this acts differently on your table

Sub DelTableRows()
  Dim i As Long
  
  Application.ScreenUpdating = False
  With ActiveSheet.ListObjects("MyTable").DataBodyRange
    For i = .Rows.Count To 1 Step -1
      If .Rows(i).Find(What:="?*", LookIn:=xlValues) Is Nothing Then
        .Rows(i).Delete
      End If
    Next i
  End With
  Application.ScreenUpdating = True
End Sub

 

[My Aswer Is This]

I have no answer.
My script works for me.
Maybe you have spaces in your cells you cannot see.

 

[Moluccanmom]

Thank you so much, this works perfectly