Issues with Auto Filter macro

[wiegs187]

Hi,

I have the following code in a macro.

(code)
With ActiveSheet
.AutoFilterMode = False
With Range("B4", Range("B" & Rows.Count).End(xlUp))
.AutoFilter 1, "*CusOrd*"
On Error Resume Next
.Offset(0).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
(code)

I want it to look at the range from B4 to the last cell in column B and delete all rows that have "CusOrd" for a value in column B. The problem is, if there are no rows with "CusOrd" in them, it will delete one of the top rows in the range B1 to B3. The offset was originally 1, but that would delete a row on the bottom that it shouldn't. How do I get it to just delete the rows with "CusOrd"? I'm not very familiar with this code (got it from a message board) or using Auto Filter in general, so any help would be greatly appreciated.

 

[Domenic]

Try...

<font face=Calibri><SPAN style="color:#00007F">With</SPAN> ActiveSheet<br>    .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Range("B4", Range("B" & Rows.Count).End(xlUp))<br>        .AutoFilter 1, "*CusOrd*"<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>        .Offset(1, 0).Resize(.Rows.Count - 1).SpecialCells(12).EntireRow.Delete<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    .AutoFilterMode = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br></FONT>

 

[wiegs187]

Thanks! That seems to be working well.

Some of my sheets are 8,000 rows or more, so this code (along with my previous code) is pretty slow. Do you know of any other ways to accomplish the same task more quickly? I was doing loops which took forever. This cut the processing time down a lot, but it's still pretty slow.

 

[Domenic]

Thanks! That seems to be working well.

You're welcome!

Some of my sheets are 8,000 rows or more, so this code (along with my previous code) is pretty slow. Do you know of any other ways to accomplish the same task more quickly? I was doing loops which took forever. This cut the processing time down a lot, but it's still pretty slow.

See if this helps...

http://www.xtremevbtalk.com/showpost.php?p=1305670&postcount=4

 

[wiegs187]

I'm getting an error message (1004 No Cells Found) if there are no cells found by the auto filter, even thoguh the "On Error Resume Next" is on there. What's the best way around this? I can't figure out why the error handler isn't working.

 

[Domenic]

I'm getting an error message (1004 No Cells Found) if there are no cells found by the auto filter, even thoguh the "On Error Resume Next" is on there. What's the best way around this? I can't figure out why the error handler isn't working.

I've just tested the code that I offered in my first post and it seem to work fine. Can you post the exact code that you're using?