VBA Code to delete the rows

[0beidat]

I want VBA Code to delete the rows when cells in column AA doesn't contain the word "Auto"

Please,, i have tried a lot of codes and it didn't work

 

[Dodgester2]

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.

 

[Michael M]

Try

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

 

[My Aswer Is This]

Try this:

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

 

[0beidat]

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.

I didn't work , i got this massege

https://ibb.co/YLcwSyj

 

[0beidat]

Try

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

It works Thank you very much

 

[Michael M]

Glad to help...

 

[Rick Rothstein]

Here is another non-looping macro that should also work...

  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

 

[My Aswer Is This]

I'm sure it's faster but I sure do not understand it. You think this is faster then filter?

Here is another non-looping macro that should also work...

  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