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

autofilter, excel, vba, visible

Solution

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

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

HTH

Problem

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?

Original source