Excel vba to delete rows based on multiple criteria

Hi experts,

I have text in Column A that is separated by some blank rows throughout.  I'd like to have code that will find certain text in Column A and delete those rows.  With the example below, what code could run through Column A of the active sheet, despite blank rows, and delete all rows with the words "Client:", "Media:" or "Product"?

Please let me know if further clarification is required.  Thank you!!!

Column A



Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Please save your spreadsheet before running this, as it cannot be undone. This assumes you have a maximum of 9999 rows:

Sub DeleteRows()
For introw = 9999 To 1 Step -1
    If InStr(Cells(introw, 1), "Client:") > 0 Or InStr(Cells(introw, 1), "Media:") > 0 Or InStr(Cells(introw, 1), "Product") > 0 Then
    End If
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xllvrAuthor Commented:
This seems to work well, Phillip!  Thanks.  If I wanted to also delete the blank rows, would it just be a matter of adding the following?:   Or InStr(Cells(introw,1), "") >0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
or Cells(introw,1)=""

Open in new window

Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

Glen RichmondSenior Software Developer.Commented:
PLease see attached and also below

Sub RemoveRows()
    '"Client:", "Media:" or "Product"?
    Dim intA As Integer
    Dim wrksht As Excel.Worksheet
    Set wrksht = Application.Worksheets("Sheet1")
    intA = 1
    Do Until intA = wrksht.UsedRange.Rows.Count
        Select Case wrksht.Cells(intA, "A").Value
            Case "Client:", "Media:", "Product:"
            Case Else
               intA = intA + 1
        End Select

    MsgBox "All Done"

End Sub

Open in new window

xllvrAuthor Commented:
Thanks for the quick reply and great solution.  Much appreciated!
Glen RichmondSenior Software Developer.Commented:
my example only loops for as many row used rather than usless cycles causing delay to code completeion..
aslo to del blank rows add "" to the case statment
 Case "Client:", "Media:", "Product:", ""
Glen RichmondSenior Software Developer.Commented:
why does no one worry about efficent code?
NorieAnalyst Assistant Commented:
Why not do the delete in one go?
Dim rngDel As Range
Dim I As Long

    For I = Range("A" & Rows.Count).End(xlUp).Row To 1 Step -1
        With Cells(I, 1)
            If .Value Like "*Client*" Or .Value Like "*Media*" Or .Value Like "*Product*" Or .Value = "" Then
                If rngDel Is Nothing Then
                    Set rngDel = .EntireRow
                    Set rngDel = Union(rngDel, .EntireRow)
                End If
            End If
        End With
    Next I

    If Not rngDel Is Nothing Then rngDel.Delete xlShiftUp

Open in new window

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
>> why does no one worry about efficent code?

Well, I don't worry about "efficent" code because, unless there is a specific reason for it, people ask questions and I like to answer questions with code that is quick to write, easier for the questioner to understand, and works, and I don't care if it takes a full second longer to run.

That's why for me. I can't answer about "no one".
Glen RichmondSenior Software Developer.Commented:
you mean you'd rather get the point by being first that do a proper job ;)
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I do a good enough job for the end user.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.