Delete row IF...........

[Dare]

Hi, Just like to say great site, and if you could help even better !

I'm looking for script to delete the row only IF cell A is blank, cell B is blank and cell C = zero.

This is for a worksheet of 6821 rows and dont want to go through them all. Thanks for your help.

 

[Peter_SSs]

Hi, Just like to say great site, and if you could help even better !

I'm looking for script to delete the row only IF cell A is blank, cell B is blank and cell C = zero.

This is for a worksheet of 6821 rows and dont want to go through them all. Thanks for your help.

Welcome to the MrExcel board!

See if this is it. I have assumed headings in row 1

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Rows()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange<br>        .AutoFilter Field:=1, Criteria1:="="<br>        .AutoFilter Field:=2, Criteria1:="="<br>        .AutoFilter Field:=3, Criteria1:="=0"<br>        .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Dare]

Thanks for the quick reply.

That worked great thanks, the only problem now its messed up other formulas in the worksheet. Is there an easy way to change that script to hide the rows instead of deleting them ?

 

[Peter_SSs]

Thanks for the quick reply.

That worked great thanks, the only problem now its messed up other formulas in the worksheet. Is there an easy way to change that script to hide the rows instead of deleting them ?

Try this:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Del_Rows()<br>    <SPAN style="color:#00007F">Dim</SPAN> rHidden <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange<br>        .AutoFilter Field:=1, Criteria1:="="<br>        .AutoFilter Field:=2, Criteria1:="="<br>        .AutoFilter Field:=3, Criteria1:="=0"<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rHidden = .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        .AutoFilter<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> rHidden <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        rHidden.EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>

 

[Dare]

Thanks for that Peter !!

All works great !