Tidying up delete row If Scipt

garypea123

Board Regular
Joined
Mar 16, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Good MOrning,

Me again :) - I would like to try and tidy up a VBA to get everything on one command.

Below, the list seems to increase and increase, but I would prefer to add the criteria to one line, as opposed to keep adding a loop.

I did try to do do the below. But it seems to delete rows which I would not expect it to delete so I think that there is something not right.


Last = Cells(Rows.Count, "B").End(xlUp).Row
For i = Last To 6 Step -1
If Cells(i, 2) Like "Gary*" or Like "John* Then
Cells(i, 2).EntireRow.Delete
End If
Next i




Existing VBA:


Last = Cells(Rows.Count, "B").End(xlUp).Row
For i = Last To 6 Step -1
If Cells(i, 2) Like "Gary*" Then
Cells(i, 2).EntireRow.Delete
End If
Next i

Last = Cells(Rows.Count, "B").End(xlUp).Row
For i = Last To 6 Step -1
If Cells(i, 2) Like "Tom*" Then
Cells(i, 2).EntireRow.Delete
End If
Next i

Last = Cells(Rows.Count, "B").End(xlUp).Row
For i = Last To 6 Step -1
If Cells(i, 2) Like "John*" Then
Cells(i, 2).EntireRow.Delete
End If
Next i

Last = Cells(Rows.Count, "B").End(xlUp).Row
For i = Last To 6 Step -1
If Cells(i, 2) Like "Keith*" Then
Cells(i, 2).EntireRow.Delete
End If
Next i
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
It needs to be
VBA Code:
If Cells(i, 2) Like "Gary*" or  Cells(i, 2) Like "John* Then
 
Solution

garypea123

Board Regular
Joined
Mar 16, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Fabulous, works a dream!! I was nearly there....Just needed do add the "Cells(i, 2)" into each variance. :)

Should have tried that before raising the post as I can see easily the difference between what I was trying to do, and the answer :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,574
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,623
Messages
5,625,940
Members
416,143
Latest member
JoyceMB

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top