Help with filter Range & delete rows

[Les]

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

 

[Guest]

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,

 

[Les]

Thanks very much for the help Gary

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
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.