Macro to assist with formatting.

[TJ Saulnier]

I have a report which is exported to excel and I want to clean it up a little before I work with the data. There are specific rows that I wish to remove since they are extraneous to my work and I'm having some difficulties.

I found the following code snippet from MaczaQ in another thread and modified the find section to look for the text that I want and I modified the result section removing the resize(2) so it only deletes the single row in which the text is located ... Works great ... however I have 6 other strings to look for and delete the corresponding row and I need some assistance in creating a loop that will look for and delete the rows containing the other 6 variables all at once.

'Originally posted by MaczaQ in another thread 
Sub deleteLoop()
    Dim result As Range
    Do
        Set result = Columns("A").Find("AAAA", lookat:=xlPart)
        'Set Result = Columns("A").Find("Employee", lookat:=xlpart) --- my update
        If result Is Nothing Then
            Exit Do
        Else
           result.EntireRow.Resize(2).Delete
           ' result.EntireRow.Delete --- my update
        End If
    Loop
End Sub

Thank you in advance for any help you might be able to offer.

[Mumps1]

Try this code. Just replace the "string1", string2", etc. with your strings.

Option Compare Text
Sub DeleteRows()
    Application.ScreenUpdating = False
    Dim bottomA As Integer
    bottomA = Range("A" & Rows.Count).End(xlUp).Row
    Dim x As Long
    For x = bottomA To 2 Step -1
        If Cells(x, 1) = "string1" Or Cells(x, 1) = "string2" Or Cells(x, 1) = "string3" Or Cells(x, 1) = "string4" Or Cells(x, 1) = "string5" Or Cells(x, 1) = "string6" Or Cells(x, 1) = "string7" Then
            Rows(x).EntireRow.Delete
        End If
    Next x
    Application.ScreenUpdating = True
End Sub

[tigeravatar]

TJ Saulnier,

Alternate...

Sub tgr()
    
    Dim rngDel As Range
    Dim rngFound As Range
    Dim strFirst As String
    Dim varFind As Variant
    
    For Each varFind In Array("Employee", "string2", "string3", "string4", "string5", "string6")
        Set rngFound = Columns("A").Find(varFind, , xlValues, xlPart)
        If Not rngFound Is Nothing Then
            strFirst = rngFound.Address
            Do
                If rngDel Is Nothing Then Set rngDel = rngFound Else Set rngDel = Union(rngDel, rngFound)
                Set rngFound = Columns("A").Find(varFind, rngFound, xlValues, xlPart)
            Loop While rngFound.Address <> strFirst
        End If
    Next varFind
    
    If Not rngDel Is Nothing Then rngDel.EntireRow.Delete
    
    Set rngDel = Nothing
    Set rngFound = Nothing
    
End Sub

[TJ Saulnier]

Hey thanks for the responses ... Tigeravatar I believe your solution is a little closer to what I'm attempting to do ... I do have a couple of questions though .. 1- the line Set rngFound = Columns .. can this be a range of columns A through F ( I neglected to mention in my original post that not all the search criteria is in column A) and 2- when i enter the strings in the array line does it matter if there is other text in the same row following the search string or will the loop evaluate to true if it finds an instance of the string amongst all the text in the cell.

[tigeravatar]

Yes on both counts

[TJ Saulnier]

Excellent .. thanks much for the assist. I have to say as well that this is without question one of the most helpful forums I've ever used. My hats off to you all.