CODEWITHSUNDEEP
excelvba
purpleblau
purpleblau

Reputation: 589

VBA Excel Autofilter highlight the unique values and sort them by color

I have two columns of data A and B. The column A contains a lot of duplicate values, but not all of them.

I want to find all unique values and mark them red and then sort them from top to bottom by its color. My code worked, but when I click the macro button again, it always shows an error message: Object variable or with block vairable not set.

It points this line to me:

ActiveWorkbook.Worksheets("Sheet1").Autofilter.Sort.SortFields.Clear

Does someone know what caused this problem when clicking the macro button twice?

The quasi working code is as follows:

    Sub sorttheuniquevaluesforme()

    Selection.Autofilter
    ActiveWorkbook.Worksheets("Sheet1").Autofilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Autofilter.Sort.SortFields.Add(Range( _
        "A1:A1000"), xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color _
        = vbRed
    
    With ActiveWorkbook.Worksheets("Sheet1").Autofilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
    End Sub

Upvotes: 0

Views: 209

Answers (1)

Francesco Giossi
Francesco Giossi

Reputation: 170

The error is caused by the previous line.

Selection.Autofilter

When you execute the 1st time, the autofilter method create the filter. Repating the command you delete the filter. So, the 2nd time you execute the code, you delete the filter, so there is no AutoFilter to be cleared in the line that actually throws the error.

On the other hand, execute just the 1st line and watch the sheet. Execute again the 1st line and you'll see the filter has gone.

Upvotes: 1

Related Questions