VBA Help

[JoeyGaspard]

Hi all, I have a spreadsheet where I am trying to use VBA to delete all rows if the cell in column B is blank, for some reason it is not working? The code I am using is listed below, any help is greatly appreciated!


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

Thanks!

 

[JoeyGaspard]

OK. Thanks for confirming.

Unfortunately, I am unable to download files from my current location (our work computers are "locked down"), but it sounds like Fluff is able to.
Hopefully, he will be able to determine what is going on.

Thank you very much for your help!

 

[JoeyGaspard]

That is asking me to sign in, you need to mark the file for sharing & then post the link that it gives you.

I found part of the problem, my lookup on the Pivot tab, is returning a zero instead of a blank

=IFERROR(VLOOKUP($A13,Lookup!$A:$E,2,0),"")

 

[Fluff]

The problem is that your data is in a table, try

'Clean up Upload
With Sheets("Upload")
        .AutoFilterMode = False
        With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*(**"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
        On Error GoTo 0
    End With
 

'Copy Data from Upload to GeneralJournal
Sheets("Upload").Select
Range(Range("A2:L2"), Range("A2:L2").End(xlDown)).Copy
Worksheets("Generaljournal").Range("B17").PasteSpecial Paste:=xlPasteValues

'Delete Blank Rows if No Data in cells in Column B
   With Sheets("GeneralJournal").ListObjects("Table1")
      .Range.AutoFilter 1, ""
      Application.DisplayAlerts = False
      .DataBodyRange.SpecialCells(xlVisible).Delete
      .Range.AutoFilter
   End With

 

[JoeyGaspard]

The problem is that your data is in a table, try

'Clean up Upload
With Sheets("Upload")
        .AutoFilterMode = False
        With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*(**"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
        End With
        .AutoFilterMode = False
        On Error GoTo 0
    End With


'Copy Data from Upload to GeneralJournal
Sheets("Upload").Select
Range(Range("A2:L2"), Range("A2:L2").End(xlDown)).Copy
Worksheets("Generaljournal").Range("B17").PasteSpecial Paste:=xlPasteValues

'Delete Blank Rows if No Data in cells in Column B
   With Sheets("GeneralJournal").ListObjects("Table1")
      .Range.AutoFilter 1, ""
      Application.DisplayAlerts = False
      .DataBodyRange.SpecialCells(xlVisible).Delete
      .Range.AutoFilter
   End With

That absolutely fixed the problem, I have never worked with a table in VBA, I have some research to do"), so thank you so much, I greatly appreciate your help!

 

[Fluff]

Glad we could help & thanks for the feedback.