excel - 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?

Answer 1

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