JohanGduToit
Board Regular
- Joined
- Nov 12, 2021
- Messages
- 89
- Office Version
- 365
- 2016
- Platform
- Windows
Afternoon Experts,
I have some VBA code that filters on 4x different wildcard values and then delete all rows containing the filtered results. For some reason, the 1st two items are filtered and relevant rows deleted; but the 3rd and 4th values are being ignored and rows containing those two wildcard items are not being deleted.
The 1st filter statement (on *Markham* xlor *Dummy* works fine; but both *Closed* and *Do Not Use* are 'being ignored'. Is this got something to do with 'xlor' maybe? Is there another way to specify the 4x values that must be filtered and deleted?
Please see code starting with 'Start Filter and Delete of unwanted Store Descriptions below.
I have some VBA code that filters on 4x different wildcard values and then delete all rows containing the filtered results. For some reason, the 1st two items are filtered and relevant rows deleted; but the 3rd and 4th values are being ignored and rows containing those two wildcard items are not being deleted.
The 1st filter statement (on *Markham* xlor *Dummy* works fine; but both *Closed* and *Do Not Use* are 'being ignored'. Is this got something to do with 'xlor' maybe? Is there another way to specify the 4x values that must be filtered and deleted?
Please see code starting with 'Start Filter and Delete of unwanted Store Descriptions below.
VBA Code:
Public Sub FormatMarkham01(sFile As String)
'Delete Worksheet <Updated Allocation List> if sheet exist, Delete Rows 1 to 4 on all remaining worksheets and Delete all Rows containing specified text on all remaining worksheets
Dim xlApp As Object
Dim xlSheet As Object
Dim I As Long
Application.SetOption "Show Status Bar", True
vStatusBar = SysCmd(acSysCmdSetStatus, "Formatting Markham Sales File (Stage 1)... Please wait.")
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
With xlApp
.Application.ScreenUpdating = False
.Application.DisplayAlerts = False
For I = .Application.Sheets.Count To 1 Step -1
If .Application.Sheets(I).Name = "Updated Allocation List" Then
.Application.Sheets(I).Delete
Else
.Application.Sheets(I).Rows(1).Resize(4).Delete
'Start Filter and Delete
.Application.Sheets(I).Select
With .Application.Range("B1", .Application.Range("B" & .Application.Rows.Count).End(xlUp))
.AutoFilter 1, "*soh*", xlOr, "*IT Nett*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.Application.Selection.AutoFilter
'End Filter and Delete
'Start Copying Even Row Cells in Column A down to Uneven (Next) Cells in Column A
With .Application.Range("A2:A" & Range("B" & .Application.Rows.Count).End(xlUp).Row)
.SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With
'End Copying Even Row Cells down
'Start Filter and Delete of unwanted Store Descriptions
With .Application.Range("A1", .Application.Range("A" & .Application.Rows.Count).End(xlUp))
.AutoFilter 1, "*Markham*", xlOr, "*Dummy*", xlOr, "*Closed*", xlOr, "*Do Not Use*"
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.Application.Selection.AutoFilter
'End Filter and Delete of unwanted Store Descriptions
End If
Next
.Application.ScreenUpdating = True
.Application.Sheets(1).Select
.Application.Range("A1").Select
.Application.ActiveWorkbook.Save
.Application.ActiveWorkbook.Close
.Quit
End With
vStatusBar = SysCmd(acSysCmdClearStatus)
Set xlSheet = Nothing
Set xlApp = Nothing
End Sub