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 sinc
there are so many rows to be deleted it decided it was to hard. So wha
it will do is just delete everything in the filter. Strange i haven'
had that happen before. So does anyone have or know of an alternativ
way to delete rows by criteria, that would be of mass fashion not lin
by line, I have tried a few of those and it would take all day long t
do that.
Thanks for the help,
Bo

--
robertjtucke
-----------------------------------------------------------------------
robertjtucker's Profile: http://www.excelforum.com/member.php...fo&userid=2180
View this thread: http://www.excelforum.com/showthread.php?threadid=47178

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

"robertjtucker"
wrote in message
news:robertjtucker.1w4oek_1128009950.246@excelforu m-nospam.com...

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