Escape My Debugger!

[rydeyz]

Okay, So I'm trying to get rid of using the debugger as a part of my process. This macro will create a new Worksheet and cut/paste the lines that contain the criteria data. If none of the lines contain the criteria, then it's supposed to skip making that particular new Worksheet. The problem is that when it does not find any of the criteria data, the debugger sends me to the "If Not rng2 Is Nothing Then rng2.EntireRow.Delete" line. I'm thinking all I need to do is tell it to skip that line if there's no data matching the criteria. I just don't know how to tell it that.

I tried End If, On Error GoTo 0,& On Error GoTo Next after that line to no avail. As of now, I just turn that line into a note line when the debugger directs me to it. Then I re-run the macro.

BTW, this is just a sample of the macro... it's actually got about 50 iterations.

Sub TabEm()
    Dim ws As Worksheet
    Dim WSNew As Worksheet
    Dim Rng As Range
    Dim rng2 As Range

On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Drill").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Rng.AutoFilter Field:=4, Criteria1:="=*drill*", Operator:=xlOr, Criteria2:="=*s/w*"
    Rng.AutoFilter Field:=5, Criteria1:="=*drill*", Operator:=xlOr, Criteria2:="=*s/w*" 'Change this number
    Set WSNew = Worksheets.Add
    WSNew.Name = "Drill" 'Change this to go with filter value
    ActiveWorkbook.Sheets("Drill").Tab.ColorIndex = 46
    ws.AutoFilter.Range.Copy
    With WSNew.Range("A1")
        .PasteSpecial Paste:=8
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
           With ws.AutoFilter.Range
            On Error Resume Next
            Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
                       .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng2 Is Nothing Then rng2.EntireRow.Delete
        End With
    End With
    ws.AutoFilterMode = False
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
    
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets("Error").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
    Rng.AutoFilter Field:=5, Criteria1:="=*error*" 'Change this number
    Set WSNew = Worksheets.Add
    WSNew.Name = "Error" 'Change this to go with filter value
    ActiveWorkbook.Sheets("Error").Tab.ColorIndex = 45
    ws.AutoFilter.Range.Copy
    With WSNew.Range("A1")
        .PasteSpecial Paste:=8
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
    With ws.AutoFilter.Range
            On Error Resume Next
           Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count) _
                       .SpecialCells(xlCellTypeVisible)
            On Error GoTo 0
            If Not rng2 Is Nothing Then rng2.EntireRow.Delete
        End With
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End With

End Sub

[rylo]

Hi

When it debugs, use the immeditate window and find out exactly what is in rng2. If that doesn't help, then build a working example with the other bits of code that are relevant (you must have some global variables or they are set in part of the code you haven't supplied) and attach it to the post for review.


rylo