• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3167
  • Last Modified:

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
Client:
Market:
Media:

Product:
Date:

Client:
Market:
Media:
Date:

Product:
0
xllvr
Asked:
xllvr
  • 4
  • 4
  • 2
  • +1
1 Solution
 
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
        Rows(introw).Delete
    End If
Next
End Sub

Open in new window

0
 
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
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
or Cells(introw,1)=""

Open in new window

0
Industry Leaders: 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!

 
Glen RichmondCustomer Reporting Programmer.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:"
               wrksht.Rows(intA).Delete
            
            Case Else
               intA = intA + 1
            
        End Select
        
    Loop

    MsgBox "All Done"

End Sub

Open in new window

RemoveRowsExample.xlsm
1
 
xllvrAuthor Commented:
Thanks for the quick reply and great solution.  Much appreciated!
0
 
Glen RichmondCustomer Reporting Programmer.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
i.e.
 Case "Client:", "Media:", "Product:", ""
0
 
Glen RichmondCustomer Reporting Programmer.Commented:
why does no one worry about efficent code?
0
 
NorieData ProcessorCommented:
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
 
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".
0
 
Glen RichmondCustomer Reporting Programmer.Commented:
you mean you'd rather get the point by being first that do a proper job ;)
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I do a good enough job for the end user.

END - UNMONITOR
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now