Solved

Excel vba to delete rows based on multiple criteria

Posted on 2014-11-06
11
2,541 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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
 
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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

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…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

770 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