option compare text
Sub MM1()
Dim lr As Long, r As Long
lr = Cells(Rows.Count, "AA").End(xlUp).Row
For r = lr To 2 Step -1
If Range("AA" & r).Value <> "Auto" Then
Rows(r).Delete
End If
Next r
End Sub
Sub Filter_Me_Please()
'Modified 12/19/2018 1:38:12 AM EST
Application.ScreenUpdating = False
Dim lastrow As Long
Dim c As Long
c = 27 ' Column Number Modify this to your need
lastrow = Cells(Rows.Count, c).End(xlUp).Row
With ActiveSheet.Cells(1, c).Resize(lastrow)
.AutoFilter Field:=1, Criteria1:="<>AUTO"
counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
If counter > 1 Then
.Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Else
MsgBox "No values found"
End If
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub
Dim l_lngNumberOfRowsInWorksheet as long, l_lngFirstRowWithData as Long, l_lngLastRowWithData as Long, l_lngCurrentRow as Long
l_lngNumberOfRowsInWorksheet = Worksheet("Sheet3").Rows.Count
l_lngFirstRowWithData = 6
l_lngLastRowWithData = Worksheet("Sheet3").Cells(l_lngNumberOfRowsInWorksheet, 27).End(xlUp).Row
For l_lngCurrentRow = l_lngLastRowWithData to l_lngFirstRowWithData Step -1
If VBA.InStr(1,Worksheet("Sheet3").Cells(l_lngCurrentRow,27).Text,"Auto",vbBinaryCompare) = 0 Then
Worksheet("Sheet3").Cells(l_lngCurrentRow,27).EntireRow.Delete
End If
Next l_lngCurrentRow
Change the "Sheet3" to what ever sheet name you need it as.
Also, if "Auto" doesn't have to be case sensitive, change "vbBinaryCompare" to "vbTextCompare" within the InStr function.
Change the row number for the first row to what ever row number the data actually starts on.
Also, for the last row of data, it assumes there is nothing below it within column AA. Purpose for the method I used to determine the last row, Excel 2007 had a pretty huge jump in the number of rows it can have compared to Excel 2003, so this makes it less version dependent.
Try
Code:option compare text Sub MM1() Dim lr As Long, r As Long lr = Cells(Rows.Count, "AA").End(xlUp).Row For r = lr To 2 Step -1 If Range("AA" & r).Value <> "Auto" Then Rows(r).Delete End If Next r End Sub
With Range("AA1", Cells(Rows.Count, "AA").End(xlUp))
.Value = Evaluate(Replace("IF(@<>""auto"",""#N/A"",@)", "@", .Address))
On Error GoTo NoAuto
.SpecialCells(xlConstants, xlErrors).EntireRow.Delete
End With
NoAuto:
End Sub
Here is another non-looping macro that should also work...
Code:With Range("AA1", Cells(Rows.Count, "AA").End(xlUp)) .Value = Evaluate(Replace("IF(@<>""auto"",""#N/A"",@)", "@", .Address)) On Error GoTo NoAuto .SpecialCells(xlConstants, xlErrors).EntireRow.Delete End With NoAuto: End Sub