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

 

[Joe4]

I cannot repeat that behavior. I created a sheet with entries in column B and in other columns.
It deleted all the rows where column B was blank, regardless of whether or not there was anything in the other columns.
That seems to suggest that column B in those rows that aren't being deleting really are not blank.
Are you sure that there isn't a single space or formula in them?

By the way, your code can be simplified to this:

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

 

[alansidman]

Try this instead.

Option Explicit

Sub DelBlnk()
    Dim i As Long
    For i = 500 To 17 Step -1
        If Range("B" & i) = "" Then
            Range("B" & i).EntireRow.Delete
        End If
    Next i
End Sub

 

[Zack Barresse]

The code looks like it will work. Are there formulas returning a null string ("") perhaps? These cells are not blank, if so, as Joe states. Perhaps you could describe your data structure for us? Depending on what you have setup, there's probably a faster way of doing this while taking into account your values.

I would posit to do this in one shot as opposed to looping, @alansidman.

 

[Fluff]

Another reason why it might not work, is if you have merged cells in the range B17:B500.

 

[JoeyGaspard]

The code looks like it will work. Are there formulas returning a null string ("") perhaps? These cells are not blank, if so, as Joe states. Perhaps you could describe your data structure for us? Depending on what you have setup, there's probably a faster way of doing this while taking into account your values.

I would posit to do this in one shot as opposed to looping, @alansidman.

Yes, the problem is, I am copying the data from a named range to the other sheet with vba, and the named range will leave formula values that you dont see unless you click on the cell, and as long as those ghost values are there, even though they are not in column b, it wont delete the row.

 

[JoeyGaspard]

Yes, the problem is, I am copying the data from a named range to the other sheet with vba, and the named range will leave formula values that you dont see unless you click on the cell, and as long as those ghost values are there, even though they are not in column b, it wont delete the row.

I replace the sheet in this spreadsheet called "Import" each week with new data, sometimes there are 200 rows, sometimes less, or more. So the sheet "BU02Data, is the dynamic range, it pulls data from "Import" and makes various calculations, and if this weeks row count is less than the previous, it leaves the ghost data. And in the section "Copy BU02Data to Atlas Upload, it will carry those blank lines to GeneralJournal which bombs my upload. Here is a my entire code string:

Sub ProcessPayroll02()


'Clear Payroll Data BU 02

Sheets("BU02Data").Select
Range("A1:G500").Select
Selection.ClearContents
Range("A1").Select



'Copy Data from Import to BU02Data
Dim Ws As Worksheet
Set Ws = Worksheets("BU02Data")
With Worksheets("Import")
.Range("A1:G1").AutoFilter 6, "500-*"
.AutoFilter.Range.Offset().Copy Ws.Range("A" & Rows.Count).End(xlUp).Offset()
.AutoFilterMode = False
End With


'Copy BU02Data Data to Atlas Upload
Sheets("GeneralJournal").Select
Range(Range("B17:M17"), Range("B17:M17").End(xlDown)).Select
Selection.ClearContents
Sheets("BU02Data").Select
Range(Range("H1:s1"), Range("H1:S1").End(xlDown)).Copy
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
Sheets("GeneralJournal").Select
Range("B17:B500").Select
Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
Range("B17").Select


End Sub

 

[Fluff]

How about

Sheets("GeneralJournal").Select
With Range("B17:B500")
   .Value = .Value
   .SpecialCells(xlBlanks).EntireRow.Delete
End With
Range("B17").Select

 

[JoeyGaspard]

How about

Sheets("GeneralJournal").Select
With Range("B17:B500")
   .Value = .Value
   .SpecialCells(xlBlanks).EntireRow.Delete
End With
Range("B17").Select

Worked perfectly! Thank you so very much!

 

[Fluff]

Glad we could help & thanks for the feedback