Selection.AutoFilter Issue

C

crmulle

I have the same code for 5 different reports. The only difference in code is
the criteria filtered and removed. All reports filter and remove the desired
data just fine except for one. The code is below and it is the same each
reports with the exception of the worksheets it is deleting and Criteria1 for
each sheet. NOTE: The BCP E&M code below filters correctly but not the
others...and they are the same code! Any help would be greatly appreciated.


Dim myLastRow As Long
Dim myLastColumn As Long
Dim myLastCell As String
Dim myRange As String
Dim Newbook As String
Dim fName As Variant
Dim MyDate As Date

Application.ScreenUpdating = False

MyDate = Date

Application.DisplayAlerts = False
Worksheets(Array("SUMMARY", "HCFG", "TOGCSG", "WBR")).Select
ActiveWindow.SelectedSheets.Delete

Sheets("New Vendors").Select

Worksheets("New Vendors").AutoFilterMode = False

Selection.AutoFilter Field:=4, _
Criteria1:="<>WHOLESALE BANKING"

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address

If myLastCell = "" Or myLastRow = "1" Then
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

Else

myRange = "D2:" & "D" & myLastRow
Range(myRange).Delete
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

End If

Sheets("Pending").Select

Worksheets("Pending").AutoFilterMode = False

Selection.AutoFilter Field:=4, _
Criteria1:="<>WHOLESALE BANKING"

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address

If myLastCell = "" Or myLastRow = "1" Then
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

Else

myRange = "D2:" & "D" & myLastRow
Range(myRange).Delete
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

End If

Sheets("BCP").Select

Worksheets("BCP").AutoFilterMode = False

Selection.AutoFilter Field:=4, _
Criteria1:="<>WHOLESALE BANKING"

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address

If myLastCell = "" Or myLastRow = "1" Then
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

Else

myRange = "D2:" & "D" & myLastRow
Range(myRange).Delete
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

End If

Sheets("BCP E&M").Select

Worksheets("BCP E&M").AutoFilterMode = False

Selection.AutoFilter Field:=4, _
Criteria1:="<>WHOLESALE BANKING"

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address

If myLastCell = "" Or myLastRow = "1" Then
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

Else

myRange = "D2:" & "D" & myLastRow
Range(myRange).Delete
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

End If
 
D

Don Guillett

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
crmulle said:
I have the same code for 5 different reports. The only difference in code
is
the criteria filtered and removed. All reports filter and remove the
desired
data just fine except for one. The code is below and it is the same each
reports with the exception of the worksheets it is deleting and Criteria1
for
each sheet. NOTE: The BCP E&M code below filters correctly but not the
others...and they are the same code! Any help would be greatly
appreciated.


Dim myLastRow As Long
Dim myLastColumn As Long
Dim myLastCell As String
Dim myRange As String
Dim Newbook As String
Dim fName As Variant
Dim MyDate As Date

Application.ScreenUpdating = False

MyDate = Date

Application.DisplayAlerts = False
Worksheets(Array("SUMMARY", "HCFG", "TOGCSG", "WBR")).Select
ActiveWindow.SelectedSheets.Delete

Sheets("New Vendors").Select

Worksheets("New Vendors").AutoFilterMode = False

Selection.AutoFilter Field:=4, _
Criteria1:="<>WHOLESALE BANKING"

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address

If myLastCell = "" Or myLastRow = "1" Then
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

Else

myRange = "D2:" & "D" & myLastRow
Range(myRange).Delete
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

End If

Sheets("Pending").Select

Worksheets("Pending").AutoFilterMode = False

Selection.AutoFilter Field:=4, _
Criteria1:="<>WHOLESALE BANKING"

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address

If myLastCell = "" Or myLastRow = "1" Then
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

Else

myRange = "D2:" & "D" & myLastRow
Range(myRange).Delete
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

End If

Sheets("BCP").Select

Worksheets("BCP").AutoFilterMode = False

Selection.AutoFilter Field:=4, _
Criteria1:="<>WHOLESALE BANKING"

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address

If myLastCell = "" Or myLastRow = "1" Then
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

Else

myRange = "D2:" & "D" & myLastRow
Range(myRange).Delete
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

End If

Sheets("BCP E&M").Select

Worksheets("BCP E&M").AutoFilterMode = False

Selection.AutoFilter Field:=4, _
Criteria1:="<>WHOLESALE BANKING"

myLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
myLastColumn = Cells.Find("*", [A1], , , xlByColumns,
xlPrevious).Column
myLastCell = Cells(myLastRow, myLastColumn).Address

If myLastCell = "" Or myLastRow = "1" Then
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

Else

myRange = "D2:" & "D" & myLastRow
Range(myRange).Delete
Selection.AutoFilter
Selection.AutoFilter
Range("A2").Select

End If
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filldown Formula 1
Find and select LastRow Minus 1 4
Problems with a method form multiple worksheets 1
operate macro across multiple sheets, error, cannot locate solutio 3
Delete cells but not charts 12
Named Range - Empty columns 8
Help to modify macro please 2
AutoFilter Criteria using named range 4

Top