#### Greta

##### New Member
Private Sub Deleterows_Click()
x = 1
y = 1
Cells(x, y).Select
Do
If Cells(x, y).Value = 0 Then
Cells(x, y).EntireRow.Delete
x = x - 1
End If
x = x + 1
Loop Until Cells(x, y) = ""
End Sub

This is the code I have assigned to a command button which checks if there are any zeros typed in column A. If any zeros or should I say 0 is found in the cell, the macro deletes the entire row. This seems to work fine in one of the workbooks I have designed. I am using the same technique and it does not seem to work in another workbook. There is no error message, the macro runs , but does not do any checking or deletion, the cursor simply selects Cell A1 of the workbook and stays there.

Is there another way of solving this problem.

If column A has any 0 then that row should be entirely deleted.

Greta

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

#### Derek

##### Well-known Member
Hi Greta

You could try this code:

Columns("A:A").Select
Selection.Find(What:="0", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
ActiveCell.EntireRow.Delete
Do
On Error GoTo errorhandler
Selection.FindNext(After:=ActiveCell).Activate
ActiveCell.EntireRow.Delete
Loop
errorhandler:
End Sub

regards
Derek

#### zacemmel

##### Well-known Member
This is "slightly" more efficient:

Sub zero()
Set myrange = Range("a1:a65536")
For i = 1 To 65536
If myrange.Item(i).Formula = 0 Then
Rows(i & ":" & i).Delete
i = i - 1
End If
Next i
End Sub

Questions?

http://www.excelquestions.com

#### Tom Urtis

##### MrExcel MVP
This is even more efficient because it does not require loops, so it's quicker and uses less system resources.

Remove the asterisks in the Replace criteria if you want to keep the number 10, or 505, or such numbers that contain zeros.

Sub DeleteRowsWIthZeros()
Range(("A1"), Range("A65536").End(xlUp)).Replace What:="*0*", Replacement:="", LookAt:=xlWhole
On Error Resume Next
Range(("A1"), Range("A65536").End(xlUp)).SpecialCells(xlBlanks).EntireRow.Delete
End Sub

#### Lurch

##### New Member
Tom Urtis or anyone,

I'm using Tom's code to delete rows with "0" in a cell.

Is it possible to put a time delay on the macro e.g. 3 seconds?

Reason: I use a web query to get data and before excel receives all the data the macro is already in full swing which in return misses some data or sometimes deletes rows with data in them.

Thanks
Lurch

#### thereuare

##### Board Regular
Somebody else can give u better directions than i on how to set this up, but why not have the macro run whenever there is a changed cell on the sheet that you run the query on?

This way the macro will run after every query (assuming there is a change in retrieved data).

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,668
Messages
5,838,680
Members
430,563
Latest member
Raeyven

### 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.

### Which adblocker are you using?

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

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