Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formula yeilds non-number result | Excel Worksheet Functions | |||
Run SQL against Access DB/Filter Results | Links and Linking in Excel | |||
Copy Filter Results only | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
How do I show the results of filter? | Excel Discussion (Misc queries) |