用VBA循环筛选列表的最简单的方法? [英] Easiest way to loop through a filtered list with VBA?
问题描述
所有已被过滤掉的隐藏行都不应该包含在内,所以从上到下的一个简单的范围没有帮助。
任何好主意?谢谢。
假设我的单元格中有1到10个数字 A2:A11
我的自动过滤器在 A1
中。我现在过滤只显示大于5的数字(即6,7,8,9,10)。
此代码只会打印可见单元格:
Sub SpecialLoop()
Dim cl As Range,rng As Range
Set rng = Range( A2:A11)
对于每个cl在rng
如果cl.EntireRow.Hidden = False然后//使用Hidden属性来检查是否过滤
Debug.Print cl
结束如果
下一个
结束Sub
也许有一个更好的方法, SpecialCells
,但上述在Excel 2003中为我工作。
编辑
只需找到一个更好的方式, SpecialCells
:
Sub SpecialLoop()
Dim cl As Range,rng As Range
设置rng =范围(A2:A11)
对于每个cl在rng.SpecialCells(xlCellTypeVisible)
Debug.Print cl
下一个cl
End Sub
If I have an auto filter set up in Excel and I want to loop through all the visible data in one column with VBA code, what's the easiest way to do this?
All the hidden rows that have been filtered away should not be included, so a plain Range from top to bottom doesn't help.
Any good ideas? Thanks.
Suppose I have numbers 1 to 10 in cells A2:A11
with my autofilter in A1
. I now filter to only show numbers greater then 5 (i.e. 6, 7, 8, 9, 10).
This code will only print visible cells:
Sub SpecialLoop()
Dim cl As Range, rng As Range
Set rng = Range("A2:A11")
For Each cl In rng
If cl.EntireRow.Hidden = False Then //Use Hidden property to check if filtered or not
Debug.Print cl
End If
Next
End Sub
Perhaps there is a better way with SpecialCells
but the above worked for me in Excel 2003.
EDIT
Just found a better way with SpecialCells
:
Sub SpecialLoop()
Dim cl As Range, rng As Range
Set rng = Range("A2:A11")
For Each cl In rng.SpecialCells(xlCellTypeVisible)
Debug.Print cl
Next cl
End Sub
这篇关于用VBA循环筛选列表的最简单的方法?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!