AutoFilter – Use of SpecialCells

Whenever Excel creates a filtered list on a worksheet, it creates a hidden named range in the background in the Name Manager. This range is not normally visible if you call up the name manager. Use the below code to make your hidden named ranges visible in the name manager (prior to using it, set a filter on a range):

Dim nvar As Name
For Each n In ActiveWorkbook.Names
    n.Visible = True

In english versions of Excel, the hidden filter range is called _FilterDatabase.My solution uses this hidden range in combination with SpeciallCells(12) to solve the problem.

My final answer does not use the hidden named ranges, but I’m leaving that info as it was part of the discovery process…

Sub test1()
Dim var As Range
Dim i As Long, ans As Long
With Sheets("Sheet1").Range("A1:C1")
    .AutoFilter 1, ">50"
        Set var = Sheet1.AutoFilter.Range
        Set var = Intersect(var.SpecialCells(12), var.Offset(1, 0))
        If Not (var Is Nothing) Then
            For i = 1 To var.Areas.Count
                var.Areas(i).Offset(0, 1).Resize(var.Areas(i).Rows.Count, 1).Value = "Check"
            Next i
        End If
End With
End Sub

I tested it with >30 and >50. It performs as expected.

Leave a Comment