Auto Filter Delete Rows by Criteria Doesn't Work Range To Complicated

That is what I got, I always use the auto filter method to delete rows, but this time I have a sheet that is 41000 rows long and I guess since there are so many rows to be deleted it decided it was to hard. So what it will do is just delete everything in the filter. Strange i haven't had that happen before. So does anyone have or know of an alternative way to delete rows by criteria, that would be of mass fashion not line by line, I have tried a few of those and it would take all day long to do that.
Thanks for the help,
Bob

"robertjtucker" <[email protected]>
wrote in message
news:[email protected]...
>
> That is what I got, I always use the auto filter method to delete rows,
> but this time I have a sheet that is 41000 rows long and I guess since
> there are so many rows to be deleted it decided it was to hard. So what
> it will do is just delete everything in the filter. Strange i haven't
> had that happen before. So does anyone have or know of an alternative
> way to delete rows by criteria, that would be of mass fashion not line
> by line, I have tried a few of those and it would take all day long to
> do that.
> Thanks for the help,
> Bob
>
>
> --
> robertjtucker
> ------------------------------------------------------------------------
> robertjtucker's Profile:
http://www.excelforum.com/member.php...o&userid=21805
> View this thread: http://www.excelforum.com/showthread...hreadid=471783
>

One way....ensure all cells in your range have a value then HIDE what you
want to keep and delete all remaining visible rows within the range:

<snipped from larger macro filtering data to just those records occuring
within last month>
'ensure all rows have a date
finalrow = range("B65536").End(xlUp).Row
With Range("B1:B" & finalrow)
..SpecialCells(xlCellTypeBlanks).Value = 0
..NumberFormat = "mm-dd-yy"
End With

'hide last month's data then delete all remaining visible rows within the
data range
BOM = "<" & DateSerial(Year(Now), Month(Now) - 1, 1)
EOM = ">" & DateSerial(Year(Now), Month(Now), 0)
With Range(Cells(1, 1), Cells(finalrow, 7))
..AutoFilter Field:=2, Criteria1:=BOM, Operator:=xlOr, Criteria2:=EOM
..Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
..SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ActiveSheet.AutoFilterMode = False 'turn off autofilter

<end snip>