VBA: Search (Range) Column, Identify Cells Matching Criteria, Select, Delete (and shift cells up)

[idrisjones]

Hi there,

I have a a sheet of data I am trying to modify automatically through VBA to inform a Dashboard. I've worked backwards, but am struggling with one of the first steps.

I am wanting to:
- Search A1:A200
- Identify cells in that range with "Prospect", "Bid", and " "
- Select them
- Delete selected cells and shift cells up

Any idea's how to do this? I am currently stuck with the 'cl' loops stopping once it hits a blank!

Thank you for your help,
Idi

 

[lrobbo314]

Try this.

Sub CleanUp()
Application.ScreenUpdating = False
Dim r As Range: Set r = Range("A1").CurrentRegion
Dim AR() As Variant: AR = r.Value
Dim txt As String

For i = UBound(AR) To LBound(AR) Step -1
    txt = AR(i, 1)
    If InStr(txt, "Prospect") > 0 Or _
    InStr(txt, "Bid") > 0 Or _
    InStr(txt, " ") > 0 Then Rows(i).Delete
Next i
Application.ScreenUpdating = True
End Sub