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]

So I am guessing by your code, that column B has a formula in it, and you are first changing it to a hard-coded value, then trying to delete.
What exactly is that formula in column B?

No, column b is blank before my macro runs and it copies data from a Pivot table, there could be data in columns c,d,e....and so on, but if the cell in column B is blank, I need the whole row deleted

 

[JoeyGaspard]

In that case can you please post all the code & answer Joe's question.

I answered Joes question above:

Sub ProcessAccruals()
Application.ScreenUpdating = False

'Refresh Pivot Table
ThisWorkbook.RefreshAll

'Copy from Pivot to Upload
Sheets("Pivot").Select
Range(Range("D2:O2"), Range("D2:O2").End(xlDown)).Copy
Worksheets("Upload").Range("A3:L3").PasteSpecial Paste:=xlPasteValues


'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
    End With


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

'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


Application.ScreenUpdating = False

End Sub

 

[JoeyGaspard]

The screenshot below shows the reults after running the macro, there should be no blank cells in column in , above or after the last line of data, as long as there is a number in column B, there should be data, if the cell in column B is blank, there should be nothing. COlumn B is labeled "Main Account"

 

[Fluff]

Thanks for that, add the line in blue as shown

        With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*(**"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
            On Error GoTo 0
        End With

You should never use on error resume next without resetting it back to default.

 

[Joe4]

After this macro runs (and does nothing), can you identify a cell in column B that should have been erased (let's say it is cell B24), and enter this formula in any blank cell and tell us what it returns?

=LEN(B24)

 

[JoeyGaspard]

Thanks for that, add the line in blue as shown

        With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            .AutoFilter 1, "*(**"
            On Error Resume Next
            .Offset(1).SpecialCells(12).EntireRow.Delete
            On Error GoTo 0
        End With

You should never use on error resume next without resetting it back to default.

 

[Fluff]

Have a look again, I used the wrong code tags originally so there was no blue line, I have corrected that.

 

[JoeyGaspard]

After this macro runs (and does nothing), can you identify a cell in column B that should have been erased (let's say it is cell B24), and enter this formula in any blank cell and tell us what it returns?

=LEN(B24)

All the cells in Column B appear to be blank after the macro, I entered the formula in cell B50, ran the macro, and that cell was empty?

 

[Joe4]

All the cells in Column B appear to be blank after the macro, I entered the formula in cell B50, ran the macro, and that cell was empty?

That is not what I meant to do. Let me try to explain again.
Don't do anything until after you have run the Macro.
After you have run it, identify some row in column B that should have been deleted, because the value in column B appears to be blank.
At that point, enter the formula I gave you in any available cell (don't put it in column B), and tell me what it returns.

So, if B50 appears to be blank after running the macro, enter this formula in any available cell (not in column B, we don't want to confuse things here):

=LEN(B50)

and tell me what number that formula returns.

Basically, we are just trying to verify that the cells in column B really are blank, and don't contain a space or some other invisible character.

 

[JoeyGaspard]

That is not what I meant to do. Let me try to explain again.
Don't do anything until after you have run the Macro.
After you have run it, identify some row in column B that should have been deleted, because the value in column B appears to be blank.
At that point, enter the formula I gave you in any available cell (don't put it in column B), and tell me what it returns.

So, if B50 appears to be blank after running the macro, enter this formula in any available cell (not in column B, we don't want to confuse things here):

=LEN(B50)

and tell me what number that formula returns.

Basically, we are just trying to verify that the cells in column B really are blank, and don't contain a space or some other invisible character.

Sorry It returns nothing, the cell i put the formula in returns blank