How to delete entier row based on creteria on a column

EagerToLearn

New Member
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.

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.

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".

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...

Code:
``````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...

Code:
``````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.

Replies
4
Views
203
Replies
5
Views
224
Replies
8
Views
410
Replies
21
Views
541
Replies
12
Views
641

1,203,264
Messages
6,054,449
Members
444,725
Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back