Solved

Excel vba to delete rows based on multiple criteria

Posted on 2014-11-06
11
2,478 Views
Last Modified: 2014-11-06
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
Client:
Market:
Media:

Product:
Date:

Client:
Market:
Media:
Date:

Product:
0
Comment
Question by:xllvr
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 500 total points
ID: 40426333
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
        Rows(introw).Delete
    End If
Next
End Sub

Open in new window

0
 
LVL 1

Author Comment

by:xllvr
ID: 40426352
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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40426357
or Cells(introw,1)=""

Open in new window

0
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40426361
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:"
               wrksht.Rows(intA).Delete
            
            Case Else
               intA = intA + 1
            
        End Select
        
    Loop

    MsgBox "All Done"

End Sub

Open in new window

RemoveRowsExample.xlsm
1
 
LVL 1

Author Closing Comment

by:xllvr
ID: 40426365
Thanks for the quick reply and great solution.  Much appreciated!
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40426373
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
i.e.
 Case "Client:", "Media:", "Product:", ""
0
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40426380
why does no one worry about efficent code?
0
 
LVL 33

Expert Comment

by:Norie
ID: 40426532
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
                Else
                    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

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40426555
>> 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".
0
 
LVL 2

Expert Comment

by:Glen Richmond
ID: 40426563
you mean you'd rather get the point by being first that do a proper job ;)
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40426571
I do a good enough job for the end user.

END - UNMONITOR
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Find word and 6 digit number 22 98
Macro 6 50
Troubleshooting a Worksheet Graphic 8 25
Copy value from a certain cell 5 24
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now