CODEWITHSUNDEEP
excelvbamacros
James Myung Hun Pak
James Myung Hun Pak

Reputation: 13

Looping through columns to find and delete rows

I am trying to create a automated way to do some clean up on a document. I am opening a txt file into excel and just dumping everything into column A. I wrote a script that will do the format the information into columns. I then need to search the doc for a string of text at which point I delete that row plus 10 rows down. I've managed to accomplish this but when it gets to the point where there is no more rows with the specified text I get a Run Time 91 error.

Just a little background, I am very new to using VBAs. So this may be a very basic fix.

I was also thinking maybe I can run a macros that looks for this string of text and deletes the rows first, that way it's only searching in column A rather than multiple columns.

Below is the code I am using. Pretty much found it online and just changed it to fit my needs

Hopefully someone can tell me what I am doing wrong or suggest another way to get this job done.

Thank you

Sub FindAndDelete()

Dim found As Range

Do Until found = False
        
    Set found = ActiveSheet.Columns("A:R").Find(what:="Based on Positions as of Month-End, No Pa", LookIn:=xlValues, lookat:=xlWhole)
    
        found.EntireRow.Select
        
        found.EntireRow.Delete
        
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
        ActiveCell.Offset(0, 0).EntireRow.Delete
     
Loop

End Sub

Upvotes: 1

Views: 42

Answers (1)

cyboashu
cyboashu

Reputation: 10433

See comments for explanation

Sub FindAndDelete()

    Dim rngFound    As Range

    Do  '/ Loop at least once before validating  condition
        '/ Use Worksheet name/codename  instead of ActiveSheet.
        Set rngFound = Sheet2.Columns("A:R").Find( _
        what:="Based on Positions as of Month-End, No Pa", _
        LookIn:=xlValues, lookat:=xlWhole)

        '/ Ensure that we found something. If Yes then delete
        '/ If you don't check this, error 91 pops up in case nothing is 
        '/ found.
        If Not rngFound Is Nothing Then
            rngFound.EntireRow.Delete
        End If
    Loop While Not rngFound Is Nothing

End Sub

Upvotes: 1

Related Questions