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 Next
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") .Range("B2:B6").Clear .AutoFilter .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 .AutoFilter End With End Sub
I tested it with >30 and >50. It performs as expected.