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

Weeks

Are you trying to add check to each visible cell in column 2? – Dayak

@TinMan, for this exercise that would Indeed do. But the question behind that would be to find the best way to do so after AutoFilter and taking into account there could potentially be no-visible cells as per my illustration above. – Weeks

You are right. I would use SpecialCells() because changing the column header will modify dependent formulas. – Dayak

Not sure if this will help but i found this very useful post re: getting visible cell row count – Bismuthic

Thanks for the pointer @Zac, I'll have a read through it. – Weeks

I think this is working as expected. Just had a quick play with it and Autofilter is behaving as designed (you could argue that the design is flawed.. but that's a whole different argument). Problem is when you apply the filter, Header row will always be visible so using .Columns(2).Value = "Check" will cause issues as you've found out. On the other hand, if after applying the filter, you use a sub range and use the same statement, it works as expected (by sub range i mean: With Sheets("Sheet1").Range("A2:B6").. Note that sub range starts from A2) – Bismuthic

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.

Sacrarium

That's amazing information. Thank you for your contribution. Did not know of the secret hidden named range. Will check it out now. Also, any clue on why the problem occurs in the first place (which is actually what I'm after) ++ – Weeks

Unfortunately, I do not know why it happens in the first place. My guess is that Excel needs to have a range to return. In the absence of results, it stores all results. Again, it's a guess. I'm as curious to know the real reason now that I've looked at the problem. – Sacrarium

Just the fact that you brought the hidden named range up is allready great information. Furthermore, I don't think you need to set var but instead can use the normal range object. Making it pretty much the exact same logic as I used. Thank you again =) – Weeks

You are right about the variable. i do it to shorten the code and keep it more readable. I was also pretty excited when i first learned about the hidden named ranges :- ) Thanks! – Sacrarium

Did not know about this hidden named range. Excellent information ++ – Bismuthic

Rohrl77, I came to know that using .AutoFilter will not work for a second run, e.g. looking for ">30". This will return the wrong secret named range. The only way to keep it working would be to use .ShowAllData to remove the filter. It's strange.... – Weeks

Can not confirm your finding. It works the same for me when i change the filter criteria. HOWEVER, what i also tried, and does seem to yield an error for me is when i change the underlying data... i.e. changing 40 to 70 in the data and then filtering for >50. Then it filters correctly, but SpecialCells no longer works as expected! Oddly, when i check the hidden named range, it does show the value as having been changed! – Sacrarium

It's the autofilter criteria! It works inconsistently. I've experimented by changing it (keeping my 70 in dataset). >40 Works. >60 Fail! <60 Works! Very odd!!! – Sacrarium

Maybe you can confirm my finding when you follow the link I posted? Untill we find the (possibly logical) explanation, I think the named range is a unreliable source to use (making AutoFilter wonky too) – Weeks

Did not see the link above. Just read it and will investigate more (time today permitting) and post back, here or in the other Q. I've never in pratice used the hidden filter range, but I agree it is unreliable. I've used the hidden named ranges mostly as a way to store variables that my users shouldn't touch. :- ) – Sacrarium

UPDATE: @Weeks This answer now works for all cases i tested. I'm keeping my fingers crossed you don't find anything. – Sacrarium

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.

Tyche

Thanks for the reply Tragamor, however unfortunately these pieces of code don't work. The first one will fill B3:B6 with "Check" and the second one will error out because there are no visible cells when searching ">50". The point in my question is that AutoFilter has got quite a usefull trick that it sees visible cells in a range itself. However, zero results takes this feature out, even to populate the whole invisible range instead! Thanks for your contribution though. Do you have a source for your last statement on the assumption it will end up selecting all cells? Again, thanks + – Weeks

The code is working for me with multiple equalities tested, although you are correct that the code can error when all rows are hidden etc. A quick dirty introduction of 'On Error Resume Next' covers the issues. You could check whether the ranges are Nothing instead though. For the last statement it's more supposition with no real evidence to back it up. The source is more what your code above does and then try and rationalise it... – Tyche

Thanks again for the reply. On Error might be "legal" in this case, however the proposed code does not look less complicated compared to what I got so far. Nonetheless, cheers for thinking along. I appreciate it =) – Weeks

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.

Cantabrigian

Hello EEM and thank you for your contribution. I'm not as sure that AutoFilter would perform as expected. Would your observation be founded on the fact that the mechanism behind autofilter NEEDS at least one visible cell to not "break"? The expected behaviour to me would be that as per code 3, it would recognize that no cells are filtered, so no values need to be entered. What is interesting in your explanation is that you prefer an On error statement over a check for visible cells in a whole range, as per my code 4. Is this generally more common practice you would say? Thank you again, ++ – Weeks

Hi @JvdV, It's not that AutoFilter needs at least one visible cell, it's more like AutoFilter does not affect (hide) the header. AutoFilter returns a variant not a range. What AutoFilter does is that changes the Hidden property of the rows (excluding the header) to TRUE for the ones that do not comply with the criteria applied and FALSE for the ones matching the criteria. Try this in a range with AutoFilter applied, hide all rows of the range (including the header), then refresh the filter, you’ll notice that the filtered rows will show but the header will remain hidden. – Cantabrigian

As regards the behavior of code 3, it is performing as designed by executing the instruction given by the line .Columns(2).Offset(1).Resize(5, 1).Value = "Check" (i.e. to update the range that starts in the second row of column 2, five cells down), The line does not include any instruction referring to visible cells. – Cantabrigian

My take with the On Error statement is in line with the SpecialCells method which returns a range, if there is zero visible cells the range is nothing then counting the visible cells would be redundant. – Cantabrigian

Note that the behavior of AutoFiletr is slightly different when applies to ListObjects. – Cantabrigian

Sorry for a late reply. You said "The line does not include any instruction referring to visible cells". Which is my point made really. AutoFilter "sees" which cells are visible in all cases but the case with zero result in the filter. In the last case it would populate all cells instead of just the few visible ones. It's exactly that behaviour that makes that we have to start using SpecialCells. Thanks for the explanation and effort again EEM + – Weeks

Also, your statement saying AutoFilter returns a variant, not a range. Are you sure about that? Documentation shows it does return a range. It probably links up with the secret named range shown by @Sacrarium – Weeks

Let us continue this discussion in chat. – Cantabrigian

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

Bismuthic

Thank you for your contribution. Indeed a lot of clever answer over here, but none so far really answered the question as to why AutoFilter does not seem to be willing to catch non-zero visible cells by itslef (as per code 3) and instead populates all cells in a hidden range. Can't wrap my head around it as yet. Again, thank you. Also for the link you posted in the comment section above. – Weeks

Yeah, i don't know the answer to that. I did some research on Friday but wasn't able to find an answer for it. I will be keeping an eye out on this post just incase someone comes up with an explanation – Bismuthic

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.

Zebapda

Thanks for your reply Aprillion, and thank you for the comparison. Unfortunately I'm unable to test the last statement because Excel on my end (2019) won't let me deselect all and press Ok. Have you found any reliable source on this unhandled edge case? + – Weeks

You can apply a numeric filter like "greater than 50", like in you VBA code. My sources are, unfortunately, summarized by "I guess". – Zebapda

Right, thanks Aprillion. Just checked and can confirm that the manual filtering will return the same results as the VBA counterpart. It seems just so unfortunate that zero results would break AutoFilter its ability to recognize which cells to fill. :( – Weeks

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

Dayak

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

Aspirate

Thank you for the contribution Patrick, it's yet another way to deal with the situation. This post is becomming sort of a gathering place for great ideas. My issue with this one would be the fact that AutoFilter creates a range itself to be used. It's a shame we can't utilize this when no results are filtered (without the use of other methods and propterties). Allthough not an answer as to why AutoFilter behaves the way it does (per code 3), I do appreciate your insight. Thanks again, ++ – Weeks

If you turn the range into a table (ListObject), you can use the listobject's DataBodyRange so the header is not within the data Range you want to make changes. – Aspirate

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

Lindsaylindsey

Thank you for the contribution. It's yet another way to test for a filter result, however I found this unreliable. Searching <30 followed by <20 will skip the second results. It would also introduce .FindNext instead of another method. Nonetheless, thanks again ++ – Weeks

The FindNext in this case needs empty cells, I added some code above. It needs a blank column next to the filter table. But It is indeed still another method. ++ – Lindsaylindsey

I can almost write a book with all different ways of achieving the same thing. However, none has answered the question yet: Why does AutoFilter creates a perfectly usable range, yet when no results are filtered it populates the whole range. Hence why we need all these properties/methods (in many forms, as per all the answers) to deal wih that. – Weeks

^ Write the book :) – Walkling

I would say that if no row is visible after filtering, excel pretends/assumes that all rows are hidden (as in hidden without the autofilter option, which will populate the complete range). – Lindsaylindsey