Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with filter Range & delete rows
Hi, i have code below that filters field 10 and then deletes the
filterd rows that are showing; this works great, but i have to filter another field afterwards and have tried to use the same code, but it then deletes everything ?? Can somebody tell me why.. Public Sub FilterCreatedBy() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Set WB = ActiveWorkbook Set SH = ActiveSheet Rows("1:1").Select Selection.AutoFilter Selection.AutoFilter Field:=10, Criteria1:="=*za-t-m-**", Operator:=xlAnd On Error Resume Next Set Rng = SH.AutoFilter.Range Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1) Set Rng = Rng.SpecialCells(xlVisible) On Error GoTo 0 If Not Rng Is Nothing Then Rng.EntireRow.Delete End If Selection.AutoFilter Field:=10 End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with filter Range & delete rows
This worked for me. I added column and criteria parameters to
'FilterCreatedBy' as well as a couple of other 'maintenance' stuff at exit_Sub: I then created a 'master' sub called TestSub and called 'FilterCreatedBy' from it using the column and criteria parameters (see below). FYI, you can make the 'FilterCreatedBy' Private with this method as long as you keep 'TestSub' in the same module. '/==============================================/ Public Sub TestSub() Call FilterCreatedBy(10, "=*za-t-m-**") Call FilterCreatedBy(2, "*a*") Call FilterCreatedBy(3, 20) End Sub '/==============================================/ Public Sub FilterCreatedBy(iColumn As Integer, varCriteria As Variant) Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Set WB = ActiveWorkbook Set SH = ActiveSheet Rows("1:1").Select Selection.AutoFilter Selection.AutoFilter Field:=iColumn, Criteria1:=varCriteria On Error Resume Next Set Rng = SH.AutoFilter.Range Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1) Set Rng = Rng.SpecialCells(xlVisible) On Error GoTo 0 If Not Rng Is Nothing Then Rng.EntireRow.Delete End If exit_Sub: On Error Resume Next Selection.AutoFilter Set Rng = Nothing Range("A1").Select Exit Sub End Sub '/==============================================/ HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Les" wrote: Hi, i have code below that filters field 10 and then deletes the filterd rows that are showing; this works great, but i have to filter another field afterwards and have tried to use the same code, but it then deletes everything ?? Can somebody tell me why.. Public Sub FilterCreatedBy() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Set WB = ActiveWorkbook Set SH = ActiveSheet Rows("1:1").Select Selection.AutoFilter Selection.AutoFilter Field:=10, Criteria1:="=*za-t-m-**", Operator:=xlAnd On Error Resume Next Set Rng = SH.AutoFilter.Range Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1) Set Rng = Rng.SpecialCells(xlVisible) On Error GoTo 0 If Not Rng Is Nothing Then Rng.EntireRow.Delete End If Selection.AutoFilter Field:=10 End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with filter Range & delete rows
Thanks very much for the help Gary
Gary L Brown wrote: This worked for me. I added column and criteria parameters to 'FilterCreatedBy' as well as a couple of other 'maintenance' stuff at exit_Sub: I then created a 'master' sub called TestSub and called 'FilterCreatedBy' from it using the column and criteria parameters (see below). FYI, you can make the 'FilterCreatedBy' Private with this method as long as you keep 'TestSub' in the same module. '/==============================================/ Public Sub TestSub() Call FilterCreatedBy(10, "=*za-t-m-**") Call FilterCreatedBy(2, "*a*") Call FilterCreatedBy(3, 20) End Sub '/==============================================/ Public Sub FilterCreatedBy(iColumn As Integer, varCriteria As Variant) Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Set WB = ActiveWorkbook Set SH = ActiveSheet Rows("1:1").Select Selection.AutoFilter Selection.AutoFilter Field:=iColumn, Criteria1:=varCriteria On Error Resume Next Set Rng = SH.AutoFilter.Range Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1) Set Rng = Rng.SpecialCells(xlVisible) On Error GoTo 0 If Not Rng Is Nothing Then Rng.EntireRow.Delete End If exit_Sub: On Error Resume Next Selection.AutoFilter Set Rng = Nothing Range("A1").Select Exit Sub End Sub '/==============================================/ HTH, -- Gary Brown If this post was helpful, please click the ''Yes'' button next to ''Was this Post Helpfull to you?''. "Les" wrote: Hi, i have code below that filters field 10 and then deletes the filterd rows that are showing; this works great, but i have to filter another field afterwards and have tried to use the same code, but it then deletes everything ?? Can somebody tell me why.. Public Sub FilterCreatedBy() Dim WB As Workbook Dim SH As Worksheet Dim Rng As Range Set WB = ActiveWorkbook Set SH = ActiveSheet Rows("1:1").Select Selection.AutoFilter Selection.AutoFilter Field:=10, Criteria1:="=*za-t-m-**", Operator:=xlAnd On Error Resume Next Set Rng = SH.AutoFilter.Range Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1) Set Rng = Rng.SpecialCells(xlVisible) On Error GoTo 0 If Not Rng Is Nothing Then Rng.EntireRow.Delete End If Selection.AutoFilter Field:=10 End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Rows Filtered within Auto Filter | Excel Programming | |||
filter and delete rows based on two criteria | Excel Programming | |||
Delete rows in filter view | Excel Worksheet Functions | |||
macro to delete duplicate rows using Filter | Excel Programming | |||
Auto Filter Delete Rows by Criteria Doesn't Work Range To Complicated | Excel Programming |