VBA Delete Row if (Tidy up required)

garypea123

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

I would like to tidy up my VBA a little bit, and have it on one command vs numerous

As an example below I have a separate command for each name I want to search, but I am sure I can tidy this up so it is only on one line :)

Thanks,
Gary
Tidy.PNG
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,839
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Change first if Line to
VBA Code:
If Cells(i, 2) Like "*Gary*" OR Cells(i, 2) Like "John*" OR Cells(i, 2) Like "Maggie*" OR Cells(i, 2) Like "Phil*" Then
 
Solution

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,066
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Try

VBA Code:
Sub DelColB()
    Dim myVar As Variant, DeleteMe As Variant
    Application.ScreenUpdating = False

    DeleteMe = Array("Gary", "John", "Maggie", "Phil")
  
    For Each myVar In DeleteMe
        Range("B6:B" & Range("B" & Rows.count).End(xlUp).Row).Replace "*" & myVar & "*", "#N/A", xlWhole, , False, False, False
    Next
  
    On Error Resume Next
    Columns("B").SpecialCells(xlConstants, xlErrors).EntireRow.Delete
    On Error GoTo 0

    Application.ScreenUpdating = True
End Sub

Edit: added the screenupdating as there are 4 replacements
 
Last edited:

garypea123

Board Regular
Joined
Mar 16, 2020
Messages
80
Office Version
  1. 365
Platform
  1. Windows
Perfect, both are different, and both work absolutely great.

Thanks Guys
 

Watch MrExcel Video

Forum statistics

Threads
1,130,190
Messages
5,640,733
Members
417,165
Latest member
Hilders1

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