Solved

Excel vba to delete rows based on multiple criteria

Posted on 2014-11-06
11
2,774 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 34

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

729 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