Excel Macro to Delete rows based on content

kennethc

New Member
Joined
Jul 7, 2008
Messages
7
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.
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Something like ?

Code:
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

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi kenneth

Here's one take on the problem:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,139
Messages
5,599,950
Members
414,350
Latest member
ajesh27

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top