deleterows.....SURPRISING PLEASE HELP !

Greta

New Member
Joined
Oct 19, 2002
Messages
19
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
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

Derek

Well-known Member
Joined
Feb 16, 2002
Messages
1,592
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
Joined
Apr 29, 2002
Messages
636
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
Joined
Feb 10, 2002
Messages
11,224
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
Joined
Dec 20, 2002
Messages
41
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
Joined
May 28, 2002
Messages
232
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,122,495
Messages
5,596,490
Members
414,070
Latest member
DuncanLucas

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
Top