DataBlake
Well-known Member
- Joined
- Jan 26, 2015
- Messages
- 781
- Office Version
- 2016
- Platform
- Windows
Hello all,
I'm trying to create a filter so that i can delete anything that contains "Atv", "Utv", "Duallie", and "Dually"
Issues arise when those phrases are sometimes capitalized, sometimes capslocked, and sometimes lowercase.
This is my first attempt with filters in VBA which does the job, but if theres a more efficient way of doing this i'm all ears.
I've been reading up and i like the sound of using Like and Ucase, but i'm unsure of how to incorporate it with what i'm trying to accomplish
I'm trying to create a filter so that i can delete anything that contains "Atv", "Utv", "Duallie", and "Dually"
Issues arise when those phrases are sometimes capitalized, sometimes capslocked, and sometimes lowercase.
This is my first attempt with filters in VBA which does the job, but if theres a more efficient way of doing this i'm all ears.
I've been reading up and i like the sound of using Like and Ucase, but i'm unsure of how to incorporate it with what i'm trying to accomplish
Code:
Sub obscureFILTER()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Set ws = ActiveSheet
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Set rng = ws.Range("B1:B" & lastRow)
With rng
.AutoFilter Field:=1, Criteria1:="=*Atv*", Criteria2:="=*Utv*", Operator:=xlOr
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ws.AutoFilterMode = False
With rng
.AutoFilter Field:=1, Criteria1:="=*ATV*", Criteria2:="=*UTV*", Operator:=xlOr
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ws.AutoFilterMode = False
With rng
.AutoFilter Field:=1, Criteria1:="=*atv*", Criteria2:="=*utv*", Operator:=xlOr
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
With rng
.AutoFilter Field:=1, Criteria1:="=*Duallie*", Criteria2:="=*Dually*", Operator:=xlOr
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ws.AutoFilterMode = False
With rng
.AutoFilter Field:=1, Criteria1:="=*DUALLIE*", Criteria2:="=*DUALLY*", Operator:=xlOr
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ws.AutoFilterMode = False
With rng
.AutoFilter Field:=1, Criteria1:="=*duallie*", Criteria2:="=*dually*", Operator:=xlOr
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
ws.AutoFilterMode = False
End Sub