使用 VBA 循环过滤列表的最简单方法? [英] Easiest way to loop through a filtered list with VBA?

问题描述

如果我在 Excel 中设置了自动过滤器，并且我想使用 VBA 代码遍历一列中的所有可见数据，那么最简单的方法是什么?

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?

不应该包括所有被过滤掉的隐藏行，因此从上到下的简单 Range 没有帮助.

All the hidden rows that have been filtered away should not be included, so a plain Range from top to bottom doesn't help.

有什么好主意吗?

推荐答案

假设我的单元格 A2:A11 中有数字 1 到 10，A1 中有我的自动过滤器.我现在过滤为仅显示大于 5 的数字(即 6、7、8、9、10).

Suppose I have numbers 1 to 10 in cells A2:A11 with my autofilter in A1. I now filter to only show numbers greater than 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

也许 SpecialCells 有更好的方法，但上述方法在 Excel 2003 中对我有用.

Perhaps there is a better way with SpecialCells but the above worked for me in Excel 2003.

编辑

刚刚通过 SpecialCells 找到了更好的方法:

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屋！