0

I was unable to find a similar question to this. Basically, I have an excel macro that I have ran probably hundreds of times and has been working fine up until today. Today, when I run it, I get a run-time error 91, object block not set.

The issue begins at this statement: quidTab.AutoFilter.Sort.SortFields.Clear

Does anybody have any advice? I am truly lost

Here is the code:


    ' Remove The flags that have not triggered on this QUID.
    Sub RemoveEmptyFlags()
    '
    ' removeEmptyFlags2 Macro
    '
        Dim filterRow As Integer, notTriggeredFlagRow As Integer, quidTab As Worksheet
     
        Range("A1").Select
        Cells.Find(What:="CE_", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
     
        filterRow = ActiveCell.Row - 1
        Rows(CStr(filterRow) & ":" & CStr(filterRow)).Select
     
        Selection.AutoFilter
        Set quidTab = ActiveWorkbook.ActiveSheet
     
        quidTab.AutoFilter.Sort.SortFields.Clear
        quidTab.AutoFilter.Sort.SortFields.Add _
            Key:=Range("C" & CStr(filterRow)), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortTextAsNumbers
        With quidTab.AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
     
        ' Find the start of the flags that have not triggered, and delete down.
        Range("C" & CStr(filterRow)).Select
        Cells.Find(What:="2", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False).Activate
        notTriggeredFlagRow = ActiveCell.Row
     
        If ActiveCell.Row > filterRow Then
     
            Rows(CStr(notTriggeredFlagRow) & ":" & CStr(ActiveWorkbook.ActiveSheet.UsedRange.Rows.Count)).Select
            Selection.Delete Shift:=xlUp
        End If
        Range("A1").Select
     
    End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Ivy McKee
  • 25
  • 4
  • ```quidtab``` is a worksheet autofilter works on ranges. – Warcupine Jan 05 '21 at 19:31
  • Does the sheet have `AutoFilter` on? Otherwise that line would throw that error, I believe. – BigBen Jan 05 '21 at 19:48
  • 1
    Ivy, you really need to rework on your code. Please avoid the use of `Select/Selection/Activeworkbook..` etc. You may want to see [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Siddharth Rout Jan 05 '21 at 20:22

0 Answers0