CODEWITHSUNDEEP
excelvbaautofilter
Siddhartha
Siddhartha

Reputation: 147

VBA Autofilter problems

I have used Macro recorder to create code for autofilter. This works the 1st time, but next time, it throws the error "object variable or block variable not set". How do I resolve this?

Sheets(SheetNum + 1).Range("A7").Activate
Range(ActiveCell, Cells(ActiveCell.End(xlDown).Row, ActiveCell.End(xlToRight).Column)).Select
    '.Selection.AutoFilter
    ' Next line gives error
    'ActiveWorkbook.Worksheets("Youth - PP").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Youth - PP").AutoFilter.Sort.SortFields.Add Key:= _
        Range("K7:K1705"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
        :=xlSortNormal
    With ActiveWorkbook.Worksheets("Youth - PP").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

I am using Excel 2007 with Windows XP. Thanks for help.

Upvotes: 0

Views: 4087

Answers (1)

PatricK
PatricK

Reputation: 6433

Removing the AutoFilter first should fix it:

ActiveWorkbook.Worksheets("Youth - PP").AutoFilterMode = False

I would recommend revise your code to avoid using .Activate, .Select, Activecell, ActiveWorkbook. Define objects and Set them.

Upvotes: 2

Related Questions