Code Interruption when Deleting Rows

[JTBorton]

I wrote a quick script to delete useless rows in a monstrous table that don't apply to the situation I am looking for. For some reason the macro keeps breaking into debug mode at the .Row(F).Delete line. It gives me the message, "Code execution has been inturrupted." I can click the 'Continue' button, but after the 70th time clicking this button my index finger begins to instigate a rebellion among fingers. I suppose if I were clicking with my middle finger this would not be a problem, because my middle finger is exercised quite often in traffic. Anyway, this is quite perplexing, because it was running fine all day yesterday when I was chopping down other tables, but then it started suddenly and without warning. Now anytime the command ".Rows(F).Delete" is executed in this workbook it breaks into debug mode, regardless of which worksheet I am chopping. Any ideas?

Sub ExtrusionSlowdown()
    Dim F           As Long
    Dim lngREnd     As Long
    Dim intSICCol   As Integer
    'Dim intDateCol  As Integer
    Dim strCatch    As String
    
    intSICCol = LocateColumn("SIC_RateLoss", wksC704Shut)
    'intDateCol = LocateColumn("enddate", wksC301MinData)
    lngREnd = LastRow(wksC704Shut)
    
    Application.ScreenUpdating = False
    With wksC704Shut
        For F = lngREnd To 2 Step -1
            strCatch = LCase(Trim(CStr(.Cells(F, intSICCol))))
            If strCatch <> "process misc" And strCatch <> "extrusion" Then
                On Error Resume Next
                [highlight].Rows(F).Delete[/highlight]
                On Error GoTo 0
            End If
            Debug.Print "Progress: " & Round((lngREnd - F) / lngREnd * 100, 2) & "%"
        Next F
    End With
    Application.ScreenUpdating = True
End Sub

-Joshua
If it's not broken, it doesn't have enough parts yet.

 

[Andrzejek]

Did you try [tt]DisplayAlerts = False[/tt], and then set it back to True?

Have fun.

---- Andy

 

[JTBorton]

Just tried it. No luck.

-Joshua
If it's not broken, it doesn't have enough parts yet.

 

[JTBorton]

Well that was odd. I tried pressing Ctrl+Break just for the heck of it while the code was already stopped in debugging mode and then let it run again. Looks like the problem went away.

-Joshua
If it's not broken, it doesn't have enough parts yet.

 

[Hoaokapohaku]

What is "wksC704Shut?" A named range on the worksheet perhaps? In any case, your macro doesn't dimension "wksC704Shut" or it's data type. If wksC704Shut is a named range on the worksheet, that doesn't make it a recognized variable in VBA. If you want to use wksC704Shut as a VBA variable you will need "DIM wksC704Shut As Range" and then set the variable with a line, " Set wksC704Shut = sheet1.range("wksC704Shut")" Example:

Sub test()
Dim wksC704Shut As Range
Set wksC704Shut = Sheet1.Range("wksC704Shut")
MsgBox wksC704Shut.Address
End Sub

 

[zelgar]

Try deleting the . before Rows

 

[JTBorton]

Hoaokapohaku,

Yes, I can see how this would be confusing without a proper explanation, and I too would have pointed it out in someone else's code had I not know what it was. "wksC704Shut" is the codename of one of the worksheets in the VBA project. "wks" is the three-letter abbreviation for a worksheet object, and the worksheet itself holds shutdown data for the C704 transfer compressor. Hence the name, "wksC704Shut." Because I changed the actual codename of the worksheet from, oh say "Sheet2", to "wksC704Shut", I can freely call it by name willy-nilly anywhere in my VBA project without declaring anything; just like you can use the codename "ThisWorkBook" anywhere in your project and VB knows what you are talking about.

zelgar,

The "." before "Rows" is necessary to tell VBA that the Row object I am referring too is associated with the wksC704Shut worksheet, and not the active worksheet in the workbook.

-Joshua
If it's not broken, it doesn't have enough parts yet.