Solved

Excel vba to delete rows based on multiple criteria

Posted on 2014-11-06
11
2,712 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Web Add-in Where is Visual Basic used 9 68
need help to look for 16 40
the UDF returns #Value when i open workbook. 19 42
count number 10 27
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…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

738 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