# Deleting rows macro

#### snideysimon

##### New Member
Hi I want to run a macro which looks in column A to see if the word "Grand" is there, if not I want the row to be deleted. I want the macro to do this for all rows in the work sheet.

i thought the below would do it, but it just seems to get stuck in a loop

Sub Delete()
'
' Delete Macro
'

'
Dim i As Long

For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A") <> "Grand" Then Rows(i).Delete

Next i

End Sub

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### bjwillingham

##### Active Member
Try

Dim i, bot as long

bot = Cells(Rows.Count, 1).End(xlUp).Row
for i = bot to 2 step -1
If Cells(i, 1) <> "Grand" Then Rows(i).Delete
next i

Perhaps the repeated evaluation for the bottom row, when it keeps changing is causing the loop. Also, I think the format for Cells(x,y) requires 'y' to be an integer, not "A").

Just a thought.

L

#### Legacy 51064

##### Guest
try:

Dim r As Long, LastRow As Long
LastRow = Range("A65536").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 1).Value <> "Grand" Then Rows(r).EntireRow.Delete
Next r

#### snideysimon

##### New Member
Tried both of these and it doesn't seem to work, does anyone know what I could be doing wrong?

#### pcc

##### Well-known Member
Does the cell contain "Grand" and just "Grand"? If not you need to use the like function eg

If Not Cells(r, 1).Value like "*Grand*" Then Rows(r).EntireRow.Delete

Replies
29
Views
1K
Replies
15
Views
311
Replies
1
Views
74
Replies
1
Views
288
Replies
7
Views
188

1,141,599
Messages
5,707,311
Members
421,502
Latest member
PULBAG

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

### Which adblocker are you using?

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