filter that yeilds no results

[Matthew Dyer]

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

 

[Ben McClave]

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

 

[Matthew Dyer]

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 headerrow 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!!

 

[Matthew Dyer]

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 headerrow 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

 

[Matthew Dyer]

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

 

[Ben McClave]

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

 

[Matthew Dyer]

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 headerrow 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!