Thread
:
Selection.AutoFilter Issue
View Single Post
#
2
Posted to microsoft.public.excel.programming
Don Guillett[_2_]
external usenet poster
Posts: 1,522
Selection.AutoFilter Issue
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
"crmulle" wrote in message
...
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
Reply With Quote
Don Guillett[_2_]
View Public Profile
Find all posts by Don Guillett[_2_]