Delete rows with specific token in Excel

I wrote the code a below, but does not seems to delete the row at all.  I am not sure why.

Sub DeleteRows1()
    'Deletes the entire row within the selection if the ENTIRE row contains no data.

    'We use Long in case they have over 32,767 rows selected.

    Dim i As Long
    Dim str As String
    Dim pos As Integer
    Dim str1 As String
    
    'We turn off calculation and screenupdating to speed up the macro.

     With Application
         .Calculation = xlCalculationManual
         .ScreenUpdating = False

         'We work backwards because we are deleting rows.

         For i = 9994 To 1 Step -1
'              If WorksheetFunction.CountA(Selection.Rows(i)) <> 0 Then
              If InStr(Cells(i, 6).Value, "TOKEN1") <> 0 Then
                     Selection.Rows(i).EntireRow.Delete
              End If
         Next i

        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

Select allOpen in new window

hi tommy,

perhaps delete this line?
If WorksheetFunction.CountA(Selection.Rows(i)) <> 0 Then

if you want to keep it, you will need a closing End If

you are using
With Application

instead of specifying a particular sheet.  If you are using the activesheet, then use:

With Application.ActiveSheet

Select allOpen in new window

also, instead of:
For i = 9994 To 1 Step -1

you can figure out the last row like this:

dim nLastRow as long

 'using worksheet object
.cells(rows.count,1).end(xlup).row
nLastRow = .Cells(.Rows.Count, 1).End(-4162).Row  'xlUp=-4162

Select allOpen in new window

this:
              If InStr(Cells(i, 6).Value, "TOKEN1") <> 0 Then
                     Selection.Rows(i).EntireRow.Delete
              End If

should be

              If InStr(.Cells(i, 6).Value, "TOKEN1") <> 0 Then
                     .Cells(i, 6).EntireRow.Delete
              End If

Select allOpen in new window

please forgive me, I am new to this forum and can't figure out how to edit ... or even if I can.

After with ActiveSheet, you should figure out nLastRow, before the loop

Hi Crystal,

You can edit, but only if your comment is the last one in the thread. To edit your comment, click the down facing arrow to the right of your name and select "Edit Comment".

Welcome to EE!

Scott

thank you, Scott  ~  and thanks for the welcome :)

Thanks for the tips.