Delete a Cell, Not a Row using Last Row Method

[Guzzlr]

LastRow = Range("B:D").Cells.Find("*", _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
MsgBox "The Last Non-Blank Row Is " & LastRow
For Each sht In ActiveWorkbook.Worksheets
    If sht.Visible And (sht.Name = "Growth") Then
        sht.Activate
        For r = Cells(Rows.Count, 2).End(xlUp).Row To 5 Step -1
            If Cells(r, 2).Value = 0 Then Cells(r).Delete
        Next r
    End If
Next sht

Hello All
I'm trying to delete the cell in Column B if it is a "0", instead of the entire row. The code above skips over and does not delete anything.
How do I get the cell to delete, essentially like deleting and shifting cells up with each deletion containing "0".

Thanks for the help
2013

 

[Joe4]

You are missing a few things, like your column reference in your Cells reference, and the shift up command.

So try changing:

Cells(r).Delete

to:

Cells(r, 2).Delete Shift:=xlUp

 

[Guzzlr]

You are missing a few things, like your column reference in your Cells reference, and the shift up command.

So try changing:

Cells(r).Delete

to:

Cells(r, 2).Delete Shift:=xlUp

That did it, thanks

 

[Joe4]

You are welcome.

 

[Rick Rothstein]

I'm trying to delete the cell in Column B if it is a "0", instead of the entire row.

Here is another way to write your code where no loops are used...

[table="width: 500"]
[tr]
	[td]Sub Test()
  On Error GoTo NoGrowthSheet
  With Sheets("Growth")
    If .Visible Then
      .Columns("B").Replace 0, "#N/A", xlWhole, , , , False, False
      .Columns("B").SpecialCells(xlConstants, xlErrors).Delete xlShiftUp
    End If
  End With
NoGrowthSheet:
End Sub
[/td]
[/tr]
[/table]