filter that yeilds no results
 

I'm trying to delete rows via results from a filter. The problem i'm running into is if there is no criteria that matches the filter then the header row is deleted. here's the Code i'm working with. Converttocol, lastcol and lastrow functions work fine. -

Set myrange = Range("A1:" & ConvertToCol(LastCol(ActiveSheet)) & lastrow(ActiveSheet))
myrange.AutoFilter
myrange.AutoFilter Field:=12, Criteria1:="=0", _
Operator:=xlOr, Criteria2:="=2E"
Range("A2:aq" & (lastrow(ActiveSheet) + 1)).SpecialCells(xlCellTypeVisible).Select
Selection.Delete
myrange.AutoFilter

filter that yeilds no results
 

Matt,

My guess is that your lastrow function returns either null or zero when there are no rows matching your criteria, so the resulting range to delete is A2:AQ1; your header row.

I think that a simple MAX function will sort this out. The sub I copied below adds a new variable (lRow) to look at the result of your (lastrow(ActiveSheet) + 1) argument and take the maximum of that figure or 2. Thus, if the lastrow function returns null, then 2 is larger and the resulting range is A2:AQ2. Hope this helps.

Ben

Dim lRow As Long
Set myrange = Range("A1:" & ConvertToCol(LastCol(ActiveSheet)) & lastrow(ActiveSheet))
myrange.AutoFilter
myrange.AutoFilter Field:=12, Criteria1:="=0", _
Operator:=xlOr, Criteria2:="=2E"
lRow = WorksheetFunction.Max(2, lastrow(ActiveSheet) + 1)
Range("A2:aq" & lRow).SpecialCells(xlCellTypeVisible).Delete
myrange.AutoFilter

filter that yeilds no results
 

On Tuesday, October 2, 2012 6:32:19 PM UTC-7, Matthew Dyer wrote:
I'm trying to delete rows via results from a filter. The problem i'm running into is if there is no criteria that matches the filter then the header row is deleted. here's the Code i'm working with. Converttocol, lastcol and lastrow functions work fine. - Set myrange = Range("A1:" & ConvertToCol(LastCol(ActiveSheet)) & lastrow(ActiveSheet)) myrange.AutoFilter myrange.AutoFilter Field:=12, Criteria1:="=0", _ Operator:=xlOr, Criteria2:="=2E" Range("A2:aq" & (lastrow(ActiveSheet) + 1)).SpecialCells(xlCellTypeVisible).Select Selection.Delete myrange.AutoFilter

Thanks for the reply Ben. I found another solution in between posting and finding your solution. I just entered an error handler that would skip the delete portion of the code in the event there is no visible data to select. I'll give your solution a shot when time allows. Again, Thanks for the Reply!!

filter that yeilds no results
 

On Tuesday, October 2, 2012 6:32:19 PM UTC-7, Matthew Dyer wrote:
I'm trying to delete rows via results from a filter. The problem i'm running into is if there is no criteria that matches the filter then the header row is deleted. here's the Code i'm working with. Converttocol, lastcol and lastrow functions work fine. - Set myrange = Range("A1:" & ConvertToCol(LastCol(ActiveSheet)) & lastrow(ActiveSheet)) myrange.AutoFilter myrange.AutoFilter Field:=12, Criteria1:="=0", _ Operator:=xlOr, Criteria2:="=2E" Range("A2:aq" & (lastrow(ActiveSheet) + 1)).SpecialCells(xlCellTypeVisible).Select Selection.Delete myrange.AutoFilter

filter that yeilds no results
 

On Tuesday, October 2, 2012 7:55:51 PM UTC-7, Matthew Dyer wrote:
On Tuesday, October 2, 2012 6:32:19 PM UTC-7, Matthew Dyer wrote: I'm trying to delete rows via results from a filter. The problem i'm running into is if there is no criteria that matches the filter then the header row is deleted. here's the Code i'm working with. Converttocol, lastcol and lastrow functions work fine. - Set myrange = Range("A1:" & ConvertToCol(LastCol(ActiveSheet)) & lastrow(ActiveSheet)) myrange.AutoFilter myrange.AutoFilter Field:=12, Criteria1:="=0", _ Operator:=xlOr, Criteria2:="=2E" Range("A2:aq" & (lastrow(ActiveSheet) + 1)).SpecialCells(xlCellTypeVisible).Select Selection.Delete myrange.AutoFilter

my code w/ error handler:
Set myrange = Range("A1:" & ConvertToCol(LastCol(ActiveSheet)) & lastrow(ActiveSheet))
myrange.AutoFilter
myrange.AutoFilter Field:=12, Criteria1:="=0", _
Operator:=xlOr, Criteria2:="=2E"
On Error GoTo After:
Range("A2:aq" & (lastrow(ActiveSheet) + 1)).SpecialCells(xlCellTypeVisible).Select
Selection.Delete
After:
myrange.AutoFilter

filter that yeilds no results
 

Matthew,

I'm glad you found a workaround. Just in case you try the code I posted and it bugs out at the delete line, you can try this modification instead:

Dim lRow As Long
Set myrange = Range("A1:" & ConvertToCol(LastCol(ActiveSheet)) & lastrow(ActiveSheet))
myrange.AutoFilter
myrange.AutoFilter Field:=12, Criteria1:="=0", _
Operator:=xlOr, Criteria2:="=2E"
lRow = WorksheetFunction.Max(2, lastrow(ActiveSheet) + 1)
If lrow 1 Then _
Range("A2:aq" & lRow).SpecialCells(xlCellTypeVisible).Delete
myrange.AutoFilter

filter that yeilds no results
 

On Tuesday, October 2, 2012 6:32:19 PM UTC-7, Matthew Dyer wrote:
I'm trying to delete rows via results from a filter. The problem i'm running into is if there is no criteria that matches the filter then the header row is deleted. here's the Code i'm working with. Converttocol, lastcol and lastrow functions work fine. - Set myrange = Range("A1:" & ConvertToCol(LastCol(ActiveSheet)) & lastrow(ActiveSheet)) myrange.AutoFilter myrange.AutoFilter Field:=12, Criteria1:="=0", _ Operator:=xlOr, Criteria2:="=2E" Range("A2:aq" & (lastrow(ActiveSheet) + 1)).SpecialCells(xlCellTypeVisible).Select Selection.Delete myrange.AutoFilter

That's the simpler way of accomplishing what I was looking for. Thanks for sharing!