当我使用AutoFilter的SpecialCells在VBA中获取可见单元格时出错 [英] Error when I use SpecialCells of AutoFilter to get visible cells in VBA

问题描述

我的主要目标是复制自动过滤器的可见单元格，然后将可见单元格的尺寸复制到新工作表。我使用这个代码：

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

最后一行我想检查值的单元格。如果我使用单元格（2,11）而不是 SpecialCells 我可以看到单元格具有工作表的所有单元格，可见且不可见。所以我想使用 SpecialCells 。

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:

错误'-2147417848（80010108）在运行时。自动化错误。

在执行类型的时候，它似乎进入一个循环，最后给出了这个错误。也许SpecialCells修改自动过滤器，然后在每个修改中再次执行autofilter？

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?

推荐答案

要处理AutoFilter的可见单元格，如果您打算排除标题，则必须使用 Offset 。你得到的错误是因为你缺少一个。之前细胞（2,11）

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.

我们的范围是

A1： F6

当您运行以下代码时，根据您是否使用 Offset ，您将获得这些结果。

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

这篇关于当我使用AutoFilter的SpecialCells在VBA中获取可见单元格时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！