Macro to Delete Blank Rows in Table

[skf786]

Hello,

my table headers are in c30:k30 and i am using a macro to paste data in the table. I am looking to add additional code to the END of this macro that deletes rows in the table that are totally blank. Cannot delete full rows of the sheet as there is more data outside the table.



thank you

KF

 

[JoeMo]

For each error message you posted, what line in the code that is running is highlighted when you receive the error messages? When you run the PostEntry macro is the activesheet the one that contains the Table, and is the table name "Table20"?

 

[skf786]

Dear Joe,

yes the table name is Table20 and everything is happening on 1 sheet. Attached is the code im using now. there is a section that is meant to delete the blank rows. However, what the macro is doing is that it is 1) running the main part of the macro smoothly, 2) instead of deleting blank rows, it is filtering blank rows and just stopping there and 3) showing an error 400

Sub PostEntry()
'
' PostEntry Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'

Application.ScreenUpdating = False

With ActiveSheet
      .Unprotect "7860"



    Range("D9:P12").Select
    Selection.Copy
    Range("D32").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("g9:g9").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
    Range("g6:g6").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
    
    Range("j9:o12").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3


Range("D20:P23").Select
    Selection.Copy
    Range("D32").Select
    Selection.End(xlDown).Select
    ActiveCell.Offset(1, 0).Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F20:O23").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    ActiveWindow.ScrollRow = 6
    ActiveWindow.ScrollRow = 5
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 3


Range("E32").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range(Selection, Selection.End(xlDown)).Select
    Range("Table20[[#Headers],[Trxn Ref]]").Select






  Range("d4").Select



With ActiveSheet.ListObjects("Table20")
        .Range.AutoFilter Field:=1, Criteria1:="="
        .DataBodyRange.EntireRow.Delete
        .Range.AutoFilter Field:=1
         End With




    .Protect "7860"

End With

    

End Sub

 

[JoeMo]

As I said in an earlier post: you can call the delete empty rows macro at whatever point you want it to execute. It works, provided the table is on the activesheet and is named whatever you entered in the delete rows macro. If you encounter a problem, post your sheet using XL2BB rather than images, noting on what line the error occurred and what the error message is.