How to delete entier row based on creteria on a column

[EagerToLearn]

Hello there,
Could some one please help me with a simple code that can delete entire row if certain criteria is met in a single cell
Example
I have a bank statement where under a first column (DATA TYPE), the cell could contain either "DATA" or "TOTAL"
How can I delete the rows contain the word "TOTAL" assuming the column is already sorted.
Thanks in advance

 

[squidgeny]

For i = 1 To 100
If Cells(1+i,1).Value = "TOTAL" Then
Cells(1+i,1).EntireRow.Delete
Next i

Change the "100" to however far down the spreadsheet goes. You won't need to sort the column for this to work.

 

[EagerToLearn]

Hello
Thanks for taking the time responding to my question.
I get the following error message when trying to exucte it
"Complie error, Next without For".
Could you please advice

 

[Rick Rothstein]

On the off-chance two rows next to each other should have the word TOTAL in them, squidgeny's loop should run backwards (by the way, he missed an End If statement, which is why you go the error). Without testing, this is what his code transforms to...

For i = 100 To 1 Step -1
  If Cells(1 + i, 1).Value = "TOTAL" Then
    Cells(1 + i, 1).EntireRow.Delete
  End If
Next i

If all the entries in your column (assumed to be Column A) are text and if you have less than 16000 rows of data, then the followng non-looping code will be faster...

With Columns("A")
  .Replace "TOTAL", "=TOTAL", xlWhole
  .SpecialCells(xlFormulas).EntireRow.Delete
End With

By the way, neither of these code snippets require your data to be sorted.