VBA for deleting 0 value only

Halim

New Member
Joined
Jan 22, 2015
Messages
25
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Use specialcells feature in excel to achieve this fast. The syntax is

Code:
<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>
 
Last edited:
Upvote 0
Perhaps

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

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

Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top