Error when I use SpecialCells of AutoFilter to get visible cells in VBA

Question

My main goal is to copy the visible cells of an autofilter and later copy the dimensions of the visible cells to the new sheet. I am using this code:

Sheets(1).AutoFilterMode = False
Sheets(1).Range("A1:A1").AutoFilter Field:=columnaNumeroIntervalo, criteria1:=CDec(paramCantidadCriterio)
Sheets(1).Range("A1:A1").AutoFilter Field:=columnaNumeroIntervaloUnidades, Criteria1:=paramUnidadesCriterio

MsgBox AutoFilter.Range.SpecialCells(xlCellTypeVisible)(2, 11).Value

With the last line I want to check the value o a cell. If I use Cells(2,11) instead of SpecialCells I can see that cells have all the cells of the sheet, visible and not visible. So I want to use SpecialCells.

If I use Special cells I get the following error:

error '-2147417848 (80010108) in runtime. Automatization error.

For the time an the type of the execution, it seem to enter in a loop, and finally gives this error. Perhaps SpecialCells modify the autofilter and then in each modification execute again the autofilter?

Forgive me if I'm missing something but I don't understand why you want to use a filter if you know you always want to return Cells(2,11). Am I missing something? — markblandford
@SiddharthRout +1 thanks, you learn something new every day. :) — markblandford
@creamyegg: There was a typo in my previous comment. I have deleted that and adding it as a part of my answer below :) — Siddharth Rout
@creamyegg: Cells(2,11) is simple check to see what happens. I wanted to know if in the cell (2,11) I have the data of the cell without filter or the data with filter. I mean that if in the data without filter in the cell (2,11) I have for example the value XXX and this is unique in all the sheet, if i filter and I get the value(2,11) y I get XXX then Cells has all the cells, not only the filtered cells. This is my case, after the autofilter, in the cell (2,11) still there is the value XXX, so I guess that Cells have all the cells, not only the filtered cells. — Álvaro García

Siddharth Rout Siddharth Rout · Accepted Answer · 2012-04-25T15:54:15

To work with the visible cells of an AutoFilter, you have to use Offset if you are planning to exclude Headers. The error you are getting is because you are missing a "." before Cells(2,11)

'~~> Remove any filters
ActiveSheet.AutoFilterMode = False

'~~> Filter, 
With rRange 
  .AutoFilter Field:=1, Criteria1:=strCriteria

  '~~> offset(to exclude headers)
  Debug.Print .Offset(1, 0).SpecialCells(xlCellTypeVisible).Cells(2,11).Value

  Debug.Print .SpecialCells(xlCellTypeVisible).Cells(2,11).Value
End With

'~~> Remove any filters
ActiveSheet.AutoFilterMode = False

I decided to add this as a part of this answer so that it might help someone else in the future.

Let's say our range is

A1:F6

enter image description here

When you run the below code, depending on whether you are using Offset or not, you will get these results.

Option Explicit

Sub Sample()
    '~~> Remove any filters
    ActiveSheet.AutoFilterMode = False

    Dim rRange As Range
    Dim Rnge As Range

    Set rRange = Sheets("Sheet1").Range("A1:F6")

    '~~> Filter,
    With rRange
      .AutoFilter Field:=1, Criteria1:="<>2"

      '~~> Offset(to exclude headers)
      Set Rnge = .Offset(1, 0).SpecialCells(xlCellTypeVisible)

      Debug.Print Range(Rnge.Address).Address
      Debug.Print ActiveSheet.Cells(3, 2).Address
      Debug.Print Range(Rnge.Address).Cells(3, 2).Address

      Debug.Print "--------------------------------------------------"

      '~~> To include headers
      Set Rnge = .SpecialCells(xlCellTypeVisible)

      Debug.Print Range(Rnge.Address).Address
      Debug.Print ActiveSheet.Cells(3, 2).Address
      Debug.Print Range(Rnge.Address).Cells(3, 2).Address

    End With

    '~~> Remove any filters
    ActiveSheet.AutoFilterMode = False
End Sub

enter image description here

HTH

Error when I use SpecialCells of AutoFilter to get visible cells in VBA

1 Answers