VBA delete blank rows

[JoeyGaspard]

I am trying to use the code below to delete the entire row if the corresponding cell in Column B is empty. It works only if there is nothing in the other columns, such as, if B5 is blank, but G5 is not, it doesnt delete the row? Thanks in advance.

'Delete Blank Rows if No Data in cells in Column B
On Error Resume Next
Sheets("GeneralJournal").Select
Range("B17:B500").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B17").Select

 

[JoeyGaspard]

Try this

Sub ProcessPayroll04()

'Remove all accounbt numbers with DUE in the description
Application.ScreenUpdating = False
With Sheets("Import")
        .AutoFilterMode = False
        With .Range("E1", .Range("E" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*Due*"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
            On Error GoTo 0
        End With
        .AutoFilterMode = False
    End With

'Remove all accountnumbers with Net Payroll Payable in the description
'With Sheets("report")
'        .AutoFilterMode = False
'        With .Range("E1", .Range("E" & Rows.Count).End(xlUp))
'            .AutoFilter 1, "*Net Payroll Payable*"
'            On Error Resume Next
'            .Offset(1).SpecialCells(12).EntireRow.Delete
'            On Error GoTo 0
'        End With
'        .AutoFilterMode = False
'    End With


'Clear BU04Data
Sheets("BU04Data").Select
  Range("A1:I150").Select
  Selection.ClearContents
  Range("A1").Select
Sheets("GeneralJournal").Select
  Range("H5").Select
  Selection.ClearContents
 
'Copy Data from Payroll Report to BU04
Dim Ws As Worksheet

Set Ws = Worksheets("BU04Data")
With Worksheets("Import")
   .Range("A1:I1").AutoFilter 1, "4"
      .AutoFilter.Range.Offset(1).Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset(0)
   .AutoFilterMode = False
End With

'Copy BU04 Data to Atlas Upload
With Sheets("GeneralJournal").ListObjects("table1").DataBodyRange
   .Rows("2:" & .Rows.Count).Delete
End With
With Sheets("BU04Data")
   .Range("L1:W" & .Range("A" & Rows.Count).End(xlUp).Row).Copy
End With
Worksheets("GeneralJournal").Range("B17").PasteSpecial Paste:=xlPasteValues

'Clear Clipboard
Application.CutCopyMode = False

'On Error Resume Next
    Sheets("GeneralJournal").Select
   
With Range("Q17:Q" & Cells(Rows.Count, "B").End(xlUp).Row)
  .Formula = "=IF([@AccountType]=""Ledger"",CONCATENATE([@[Main account]],""-"",[@BusinessUnit],""-"",[@Department],""-"",[@CostCenter],""-"",[@CIP],""-""),IF(OR([@AccountType]=""Customer"",[@AccountType]=""Vendor"",[@AccountType]=""Fixedassets""),SUBSTITUTE([@[Main account]],""-"",""\-"",1),[@[Main account]]))"

End With

'Delete Blank Rows if No Data in cells in Column B
'On Error Resume Next
     With Sheets("GeneralJournal").ListObjects("Table1").ListColumns("Main account").DataBodyRange
   .Value = .Value
   On Error Resume Next
   .SpecialCells(xlBlanks).Delete
   On Error GoTo 0
End With
Range("B17").Select
Application.ScreenUpdating = True
End Sub

That seems to work great! Can you tell me what the issue was? Even if you dont, thank you so very much!

 

[Fluff]

Not entirely sure. I don't use Tables that much, so don't know all the ins & outs of them.
It appeared that if you had a contiguous range it would delete the entire column, but don't know why.

 

[Zack Barresse]

Tables can be finicky. To utilize the ListObject object instead of a Range object, you could use something like the below code.

Some things to note about ListObjects, as handled below:

• Never assume the DataBodyRange is instantiated - if it's not, you'll have to use the InsertRowRange object instead
• Never assume the totals row is showing, if referenced and it's not showing, an error is thrown (same with headers)
• Formulas only need to be put in one time, so long as they're actual calculated columns

Sub ProcessPayroll04()

    Dim DataSheet As Worksheet
    Dim ImportSheet As Worksheet
    Dim JournalSheet As Worksheet
    Dim JournalTable As ListObject
    Dim JournalTotalRow As Boolean

    Set DataSheet = ThisWorkbook.Worksheets("BU04Data")
    Set ImportSheet = ThisWorkbook.Worksheets("Import")
    Set JournalSheet = ThisWorkbook.Worksheets("GeneralJournal")
    Set JournalTable = JournalSheet.ListObjects("Table1")

    Application.ScreenUpdating = False

    'Remove all accounbt numbers with DUE in the description
    ImportSheet.AutoFilterMode = False
    ImportSheet.Range("E1", ImportSheet.Range("E" & Rows.Count).End(xlUp)).AutoFilter 1, "*Due*"
    On Error Resume Next
    ImportSheet.Range("E1", ImportSheet.Range("E" & Rows.Count).End(xlUp)).Offset(1).SpecialCells(12).EntireRow.Delete
    On Error GoTo 0
    ImportSheet.AutoFilterMode = False

    'Copy BU04 Data to Atlas Upload
    If Not JournalTable.DataBodyRange Is Nothing Then JournalTable.DataBodyRange.Delete
    JournalTotalRow = JournalTable.ShowTotals
    JournalTable.ShowTotals = False
    DataSheet.Range("L1:W" & DataSheet.Range("A" & Rows.Count).End(xlUp).Row).Copy
    JournalTable.InsertRowRange.PasteSpecial xlPasteValues
    JournalTable.ShowTotals = JournalTotalRow
    Application.CutCopyMode = False

    'Delete Blank Rows if No Data in cells in Column B
    JournalTable.ListColumns("Main account").DataBodyRange.Replace 0, "", xlWhole
    On Error Resume Next
    Intersect(JournalTable.DataBodyRange, JournalTable.ListColumns("Main account").DataBodyRange.SpecialCells(xlBlanks).EntireRow).Delete
    On Error GoTo 0

    Application.ScreenUpdating = True

End Sub