VBA for deleting 0 value only

[Halim]

Hi, I am creating the following vba, aims for deleting 0 value only in the range P7:P208, but the vba is also deleting any number containing 0 such as 10, 20, 30 and so on.

Kindly please help to correct the VBA.
Private Sub CommandButton3_Click()
Dim c As Range
Dim SrchRng
Set SrchRng = ActiveSheet.Range("P7:P208", ActiveSheet.Range("P208").End(xlDown))
Do
Set c = SrchRng.Find("0", LookIn:=xlValues)
If Not c Is Nothing Then c.EntireRow.Delete
Loop While Not c Is Nothing
End Sub

Txs Halim

 

[Hindustani]

Use specialcells feature in excel to achieve this fast. The syntax is

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#333333][FONT=Verdana]ActiveSheet.Range("P7:P208")[/FONT][/COLOR][COLOR=#000000].[/COLOR][COLOR=#000000]SpecialCells[/COLOR][COLOR=#000000]([/COLOR][COLOR=#000000]xlCellTypeBlanks[/COLOR][COLOR=#000000])[/COLOR][COLOR=#333333][FONT=Verdana]EntireRow.Delete[/FONT][/COLOR][COLOR=#000000]


[/COLOR]</code>

 

[VoG]

Perhaps

Set c = SrchRng.Find("0", LookIn:=xlValues, lookat:=xlWhole)

 

[Halim]

Perhaps

Set c = SrchRng.Find("0", LookIn:=xlValues, lookat:=xlWhole)

Thanks, this code work perfectly...

 

[vcoolio]

Hello Halim,

I'm a little late here but did you consider using autofilter instead of a loop? Its a lot tidier:-

Sub DeleteZeros()

Application.ScreenUpdating = False

With ActiveSheet
    .AutoFilterMode = False
    With Range("P7", Range("P" & Rows.Count).End(xlUp))
        .AutoFilter 1, "0"
        On Error Resume Next
        .Offset(1).SpecialCells(12).EntireRow.Delete
    End With
    .AutoFilterMode = False
End With

Application.ScreenUpdating = True

End Sub

and a somewhat faster method.

Anyway, another option for you.

Cheerio,
vcoolio.