Excel Macro to Delete rows based on content

[kennethc]

Hi,

I want to create a macro in Excel 2003 that will search every row of a sheet for the words "Account Information", "Currency" and "Ledger". If it finds a row with any occurence of the words listed above it should delete that row.
What I have so far is the following:
Sub Tidy_Up()
Dim rng As Range
Dim what As String
what = "Account Information"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub

How do I incorporate the search for "Currency" and "Ledger"?

Many thanks in advance.

Kenneth.

 

[DonkeyOte]

Something like ?

Sub Tidy_Up()
Dim rng, cell As Range
Dim what As Variant: what = Array("Account Information", "Currency", "Ledger")
For i = 0 To UBound(what)
    Set rng = ActiveSheet.UsedRange.Find(what(i))
    Select Case rng Is Nothing
        Case False
            For Each cell In rng
                Rows(cell.Row).Delete
            Next cell
    End Select
Next i
End Sub

 

[Richard Schollar]

Hi kenneth

Here's one take on the problem:

Sub Tidy_Up()
Dim rng As Range
Dim what As String, arrValues(1 To 3, 1 To 2)
arrValues(1, 1) = "Account Information": arrValues(1, 2) = True
arrValues(2, 1) = "Currency": arrValues(2, 2) = True
arrValues(3, 1) = "Ledger": arrValues(3, 2) = True
Do
    If arrValues(1, 2) Then
        Set rng = ActiveSheet.UsedRange.Find(arrValues(1, 1))
        If rng Is Nothing Then
            arrValues(1, 2) = False
            If arrValues(2, 2) Then
                Set rng = ActiveSheet.UsedRange.Find(arrValues(2, 1))
                If rng Is Nothing Then
                    arrValues(2, 2) = False
                    If arrValues(3, 2) Then
                        Set rng = ActiveSheet.UsedRange.Find(arrValues(2, 1))
                        If rng Is Nothing Then
                            arrValues(3, 2) = False
                        Else
                            rng.EntireRow.Delete
                        End If
                    End If
                Else
                    rng.EntireRow.Delete
                End If
            End If
        Else
            rng.EntireRow.Delete
        End If
    End If
Loop While arrValues(1, 2) Or arrValues(2, 2) Or arrValues(3, 2)
End Sub

 

[kennethc]

Excellent, many thanks for your assistance - much appreciated!