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]

Glad we could help & thanks for the feedback.

Hey Fluff, I just realized that after running the macros 1 time each, the code you gave me yesterday deletes column B on the generalJournal sheet! Any ideas?

 

[Fluff]

Are you saying it delete the entire column, or just clears the B17 downwards?

 

[JoeyGaspard]

Are you saying it delete the entire column, or just clears the B17 downwards?

it removes the "main account" table and moves all the other column headings 1 column to the left

 

[Fluff]

Have you changed the code at all, because it doesn't do that for me.

 

[JoeyGaspard]

Have you changed the code at all, because it doesn't do that for me.

I have not changed anything, other than clicking process bu03, then process bu04, this is what I have:
On Error Resume Next
Sheets("GeneralJournal").Select
With Range("B17:B500")
.Value = .Value
.SpecialCells(xlBlanks).EntireRow.Delete
End With
Range("B17").Select
Application.ScreenUpdating = True
End Sub

 

[Fluff]

That's the bit of code I said to change in post#18.

 

[JoeyGaspard]

Have you changed the code at all, because it doesn't do that for me.

here is the spreadsheet again, just to make sure

BU0304 Upload.xlsm

Shared with Dropbox

www.dropbox.com

 

[JoeyGaspard]

That's the bit of code I said to change in post#18.

It is changed, sorry I sent you the wrong code. I just shared the spreadsheet with you, if you click either of the process buttons, you will see "Main Account" disappear.

 

[JoeyGaspard]

That's the bit of code I said to change in post#18.

BU0304 Upload.xlsm

Shared with Dropbox

www.dropbox.com

 

[Fluff]

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