AutoFilter – Use of SpecialCells

by

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.

UPDATE
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.

More Related Contents:

  1. Excel VBA autofilter all but three
  2. Autofilter with column formatted as date
  3. ShowAllData method of Worksheet class failed
  4. Format Cell Contents
  5. Excel formula checklist in employees
  6. How do I put double quotes in a string in vba?
  7. Excel VBA – How to add dynamic array formula
  8. How can I URL encode a string in Excel VBA?
  9. Compare double in VBA precision problem
  10. Row count on the Filtered data
  11. VBA – Write all possible combinations of 4 columns of data [duplicate]
  12. Remove duplicates from array using VBA
  13. 2 ways for “ClearContents” on VBA Excel, but 1 work fine. Why? [duplicate]
  14. How to automatically insert a blank row after a group of data
  15. Get File Path (ends with folder)
  16. Get length of array?
  17. Email a single attachment from folder of files each to a different person
  18. Bad data selection using VBScript , on date field values
  19. Excel error 1004 “Unable to get …. property of WorksheetFunction class” appearing inconsistently
  20. Sending object array to variant works before August 2019 Windows update, but fails after update
  21. Find last row in range
  22. How can I install/use “Scripting.FileSystemObject” in Excel 2010 for MAC?
  23. Differences between Excel’s Form Controls & ActiveX Controls
  24. Excel VBA User Defined Function that counts cells with conditional formatting
  25. Get Timezone Information in VBA (Excel)
  26. Excel is calculating a formula with a VBA function as an error unless it is re-entered
  27. How to implement custom iterable class in VBA
  28. move files from one folder to another
  29. VBA Excel simple Error Handling
  30. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?

Leave a Comment