VBA Code Not Working

[julia55]

I call the below macro from another macro. It works sometimes, but not every time. I can't figure out why it isn't working, because the text the macro searches for is there, but for some reason I get an error somtimes when it runs. I am getting run time error '424' object required. Any help would be greatly appreciated.

Sub DeleteRowsTOTALONLY()
    Dim c As Range
    Dim SrchRng
    Set SrchRng = ActiveSheet.Range("A1", ActiveSheet.Range("I65536").End(xlUp))
    Do
        Set c = SrchRng.Find("Total Only", LookIn:=xlValues)
        If Not c Is Nothing Then c.EntireRow.Delete
    Loop While Not c Is Nothing
End Sub

 

[Norie]

Is the error on the Loop line?

If it is it's probably because c doesn't even exist if you've deleted it in the line above.

ie c.EntireRow.Delete deletes everything including c.

What is it you actually want to delete?

Is it rows with a particular value in a particular column?

 

[julia55]

I am trying to delete rows that contain the specific text in any column. The number of rows the text is found in can change, and the column it is found in can change. I actually do this for a few different strings of text and every other one works except this one.

The error is coming up on the "Set c = SrchRng.Find("Total Only", LookIn:=xlValues)" line, and I believe it comes up after it does delete one line.

 

[Norie]

Well I'm pretty sure it's happening because c is getting deleted but that if that's the case the same exact code should also error for different strings.


Do you want to delete entire rows if a particular value is found in any column in that row?

You could try using something other than find.

Sub DeleteRowsTOTALONLY()
Dim rng As Range
Dim I As Long
Dim LastRow As Long
 
    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For I = LastRow To 1 Step -1
 
        Set rng = ActiveSheet.Range("A" & I).Resize(, 9)    ' sets range to Ax:Ix, where x=I
 
        If Application.WorksheetFunction.CountIf(rng, "Total") > 0 Then rng.EntireRow.Delete

    Next I

End Sub

 

[julia55]

I can't get the code to actually delete anything. I like the idea of using the Next function much better, but I'll have to figure out how to make it work.

 

[Norie]

What code isn't working?

If it's the code I posted, it worked for me but I used a small, simple data set on a workbook with one worksheet.

One thing that I can see being a problem is the use of ActiveSheet.

If, like most workbooks, yours has more than one worksheet then the code might not be running against the right one.

 

[julia55]

The code you pasted wasn't working, but I did figure it out now. I do need it to run on the active sheet, because the same macro will end up running for 4 sheets. The cell it was leaving behind contains "Total Only JK-DELAR" so I think it was looking for an exact match. I modified the code a little bit and now it is working. Thanks so much for your help Norie!

Sub DeleteRowsTOTALONLY()
Dim rng As Range
Dim I As Long
Dim LastRow As Long
 
    LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
    For I = LastRow To 1 Step -1
 
        Set rng = ActiveSheet.Range("A" & I).Resize(, 9)    ' sets range to Ax:Ix, where x=I
 
        If Application.WorksheetFunction.CountIf(rng, "*Total Only*") > 0 Then rng.EntireRow.Delete
    Next I
 
End Sub