deleterows.....SURPRISING PLEASE HELP !

[Greta]

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.

Can anyone please help me.!!

Greta

 

[Derek]

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]

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]

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]

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]

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