使用 AutoFilter 的 SpecialCells 在 VBA 中获取可见单元格时出错
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10319096/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Error when I use SpecialCells of AutoFilter to get visible cells in VBA
提问by álvaro García
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 SpecialCellsI can see that cells have all the cells of the sheet, visible and not visible. So I want to use SpecialCells.
最后一行我想检查 oa 单元格的值。如果我使用Cells(2,11)而不是SpecialCells我可以看到单元格具有工作表的所有单元格，可见和不可见。所以我想用SpecialCells.
If I use Special cells I get the following error:
如果我使用特殊单元格，则会出现以下错误：
error '-2147417848 (80010108) in runtime. Automatization 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?
对于一次执行的类型，好像进入了一个循环，最后给出了这个错误。也许 SpecialCells 修改自动过滤器，然后在每次修改中再次执行自动过滤器？
回答by Siddharth Rout
To work with the visible cells of an AutoFilter, you have to use Offsetif you are planning to exclude Headers. The error you are getting is because you are missing a "." before Cells(2,11)
要使用自动筛选器的可见单元格，Offset如果您打算排除标题，则必须使用。你得到的错误是因为你缺少一个“。” 前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
A1:F6
When you run the below code, depending on whether you are using Offsetor not, you will get these results.
当您运行以下代码时，根据您是否使用Offset，您将获得这些结果。
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
HTH
