filter that yeilds no results

  • Thread starter Thread starter Matthew Dyer
  • Start date Start date
M

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
 
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
 
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
Click to expand...

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!!
 
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
Click to expand...
 
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
 
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
 
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
Click to expand...

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