AutoFilter - Use of SpecialCells

Question

_Background:

For many times I have applied AutoFilter and never really asked myself why it works the way it does sometimes. Working with the results of filtered data can be confusing at times, especially when SpecialCells comes into play.

Let me elaborate with the below scenario:

---

_{Test data:}

| Header1 | Header2 |
|---------|---------|
| 50      |         |
| 10      |         |
| 30      |         |
| 40      |         |
| 20      |         |

---

_{Code 1 - Plain AutoFilter:}

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        .Columns(2).Value = "Check"
    .AutoFilter
End With

This will work (even without the use of SpecialCells(12)), but will populate B1.

---

_{Code 2 - Using .Offset:}

To prevent the above behaviour we can implement Offset like so:

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        .Columns(2).Offset(1).Value = "Check"
    .AutoFilter
End With

However, this will now populate the row below our data, cell B7.

---

_{Code 3 - Using .Resize:}

To prevent .Offset to populate B7 we must now include a .Resize:

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With

Allthough now we both prevented B1 and B7 to be populated we got B2:B6 populated, the AutoFilter mechanism appears to be "broken". I tried to show it with the below screenshots. The middle one is when filtered on ">30" and the right one when filtered on ">50". As I see it, this will have to do with the fact that the referenced range now consists of zero visible cells.

---

_{Code 4 - Using .SpecialCells:}

The normal thing for me to do here would to Count the visible cells first (including the headers in the range to prevent an error 1004).

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        If .SpecialCells(12).Count > 2 Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With

---

_Question:

As you can see, I went from .Columns(2).Value = "Check" all the way to If .SpecialCells(12).Count > 2 Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check", just to prevent B1 to be overwritten.

Apparently, AutoFilter mechanism does work very well in the first scenario to detect visible rows itself, but to prevent the header to be overwritten I had to implement:

• Offset
• Resize
• SpecialCells(12)

Am I overcomplicating things here and would there be a shorter route? Also, why does a whole range of invisible cells get populated once no cells are visible. It would work well when there is actually some data filtered. What mechanism does this (see code 3)?

The, not so very elegant (IMO), option I came up with is to rewrite B1:

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
        Var = .Cells(1, 2): .Columns(2).Value = "Check": .Cells(1, 2) = Var
    .AutoFilter
End With

Answer 1

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.

Answer 2

The issues are obviously stemming from dealing with the hidden rows within the table, so the easiest way to deal with this is to create a table body range you can manipulate and review the visible cells.

If you want to mark visible rows it is a bit easier than the hidden rows as otherwise you would need to create a dummy variable, unhide, fill the blanks then delete the dummy variables

For example

Sub AutoFilterTable()

    Dim SrcRange As Range: Set SrcRange = Sheets("Sheet1").Range("A1:B6")
    Dim BodyRange As Range: Set BodyRange = Application.Intersect(SrcRange, SrcRange.Offset(1, 0))

    With SrcRange
        BodyRange.Columns(2).ClearContents
        .AutoFilter 1, ">30"
        On Error Resume Next
        BodyRange.Columns(2).SpecialCells(xlCellTypeVisible) = "Check"
        .AutoFilter
    End With

End Sub

Using a dummy variable

Sub AutoFilterTable()

    Dim SrcRange As Range: Set SrcRange = Sheets("Sheet1").Range("A1:B6")
    Dim BodyRange As Range: Set BodyRange = Application.Intersect(SrcRange, SrcRange.Offset(1, 0))

    With SrcRange
        BodyRange.Columns(2).ClearContents
        .AutoFilter 1, ">30"
        On Error Resume Next
        BodyRange.Columns(2).SpecialCells(xlCellTypeVisible) = "Dummy"
        .AutoFilter
        BodyRange.Columns(2).SpecialCells(xlCellTypeBlanks) = "Check"
        BodyRange.Columns(2).Replace "Dummy", ""
    End With

End Sub

Then the question around code 3: it depends on whether .Columns(2).Offset(1) is a hidden row or not (and whether the others are hidden or not)

If visible then it will work as expected; indeed whether hidden or not if rows exist that are visible, resizing over the top of them will end up selecting the visible cells. If all the rows are hidden though, the Offset range is still 'active' so when it is resized with no visible cells to subsume the range, it ends up selecting all the cells.

Answer 3

Range.AutoFilter method (Excel) is performing as expected, as it is applying filter criteria to a range. Range.SpecialCells method (Excel) it is also performing as expected as it is returning an union range with the visible cells encountered in the range to which it is applied.

The unexpected result is generated when the SpecialCells method is applied to the entire range Range("A1:B6"), as the header is visible then it is included in the resulting range.

As the SpecialCells method might return an union range (several areas), it is suggested to applied it to the "target" Range we'll need to update with the value "Check", i.e. Column(2).

We also need to use the On Error statement to manage the cases when AutoFilter returns no visible rows.

The following procedure apply both filters updating the respective values.

Sub Range_AutoFilter()

    With ThisWorkbook.Sheets(1).Range("A1:B6")

        .AutoFilter 1, ">30"
        On Error Resume Next 
        .Cells(2, 2).Resize(-1 + .Rows.Count) _
            .SpecialCells(xlCellTypeVisible).Value2 = "Check >30"
        On Error GoTo 0
        .AutoFilter

        .AutoFilter 1, ">50"
        On Error Resume Next
        .Cells(2, 2).Resize(-1 + .Rows.Count, 1) _
            .SpecialCells(xlCellTypeVisible).Value2 = "Check >50"
        On Error GoTo 0
        .AutoFilter

    End With

    End Sub

This not different from the other answers just wanted to add an explanation about the "unexpected results" due to the way the methods were applied.

Answer 4

There are a lot of clever approaches here. Mines a bit more archaic but seems to work (i've tested it with the table provided in the question)

Sub SetFilteredCell()

    Dim oWS As Worksheet: Set oWS = ThisWorkbook.Worksheets("Sheet4")               ' Change sheet reference
    Dim iLRow As Long: iLRow = oWS.Range("A" & oWS.Rows.Count).End(xlUp).Row        ' Presuming that first row is the header
    Dim oRng As Range: Set oRng = oWS.Range("A1:B" & iLRow)                         ' Set range here
    Dim rFilteredRng As Range
    Dim oCRng As Range

    ' Clear any existing filter
    oWS.AutoFilterMode = False

    ' Set autofilter
    oRng.AutoFilter Field:=1, Criteria1:=">20"

    ' Check if autofilter returned any rows
    If oWS.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count - 1 > 0 Then

        ' Set filtered range
        Set rFilteredRng = oRng.Cells.SpecialCells(xlCellTypeVisible)

        ' Loop through all filtered rows
        For Each oCRng In rFilteredRng.Rows

            ' Skipping first row as the presumption is that its the header row
            If oCRng.Row > 1 Then
                oWS.Cells(oCRng.Row, 2).Value = "Check"
            End If

        Next

    End If

    ' Clear filter
    oWS.AutoFilterMode = False

End Sub

Reference: This question

Answer 5

That's just the exactly same behaviour as when performing those steps manually:

• apply Autofilter to a range
• select 2nd column of that range (including top row)
• filter out everything (so only top row is visible)
• type a new value and insert to whole range by Ctrl+Enter (only the top row of 2nd column is affected)

Now, if you press down arrow before typing (same as .Offset(1)), the next visible cell will be selected (B7).

If you manually select range B2:B6 before applying the autofilter (so all cells are filtered out), and insert a value with Ctrl+Enter, all cells will be affected - I guess that was an unhandled edge case for manual Autofilter (people have not tried to insert value only to hidden cells), even if not ideal when working with VBA Autofilter.

Answer 6

Here is another variation that chains SpecialCells xlCellTypeConstants and xlCellTypeVisible to trim the target range.

With Range("A1:B6")

    .Offset(1).Columns(2).ClearContents
    .AutoFilter 1, ">50", , , True

    On Error Resume Next
     .Offset(1).SpecialCells(xlCellTypeConstants).SpecialCells(xlCellTypeVisible).Columns(2) = "Checked"
    On Error GoTo 0

    .AutoFilter
End With

Answer 7

When you want to change the databody of the range after filter, you should use Intersection of the original range offset by 1 row (to leave out header) and the SpecialCells(xlCellTypeVisible), then go through the Areas.

Clues are in this example:

Option Explicit

Sub MoreThan50()
    MoreThanValue "50"
End Sub

Private Sub MoreThanValue(Optional Amount As String = "")
    Dim oRng As Range, oRngArea As Range, oRngResult As Range
    Set oRng = Sheets(1).Range("A1:B6")
    ' Clear Previous data on 2nd column
    With Intersect(oRng, oRng.Offset(1))
        .Columns(2).ClearContents
    End With
    With oRng
        ' Apply AutoFilter
        .AutoFilter 1, ">" & IIf(Len(Amount) = 0, "50", Amount)
        ' Update 2nd Column of resulting data
        Set oRngResult = Intersect(oRng.Offset(1), .SpecialCells(xlCellTypeVisible))
        If Not oRngResult Is Nothing Then
            With oRngResult
                If .Areas.Count > 0 Then
                    For Each oRngArea In .Areas
                        oRngArea.Columns(2).Value = "check"
                    Next
                End If
            End With
            Set oRngResult = Nothing
        End If
        .AutoFilter
    End With
    Set oRng = Nothing
End Sub

Answer 8

I think (With Limited testing, Cells in Column 2 must be empty) you can use .FindNext. No need for SpecialCells and on error statements,

I added this in your code 3:

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">50"
       If Not .FindNext(.Cells(1)) Is Nothing Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With

EDIT: Assuming there's a blank column next to your FilterTable

With Sheets("Sheet1").Range("A1:B6")
    .AutoFilter 1, ">30"
          If Not .Offset(, 1).FindNext() Is Nothing Then .Columns(2).Offset(1).Resize(5, 1).Value = "Check"
    .AutoFilter
End With