如何获取Excel自动筛选后的有效行号以提取指定列数据？

应用筛选的代码 #

我正在对工作表应用筛选：

' 对FORMATTED_INC_DATE_MONTH_YEAR和PRIORITY3列应用筛选
sourceRange.Range("DB1").AutoFilter field:=106, Criteria1:=strEOM_Selected
sourceRange.Range("DE1").AutoFilter field:=109, Criteria1:="Critical", Operator:=xlOr, Criteria2:="High"

遍历筛选行的尝试 #

我需要仅遍历筛选后的行，从预定义列中提取多个值，尝试的代码如下：

' 遍历筛选后的行，仅将相关列的值复制到源工作表
Set rngFiltered_Range = sourceRange.Range("DB1").SpecialCells(xlCellTypeVisible).EntireRow
    Debug.Print rngFiltered_Range.Address
    For Each rng In rngFiltered_Range
        If rng.Row <> 1 Then   '忽略表头行   
            Debug.Print rng.Address            
            strPRIORITY3 = rng.Cells(rng.Row, 109)                                                                                       
            strINC_DATE = rng.Cells(rng.Row, 4)                                                                                        
            strINC_NUM = rng.Cells(rng.Row, 1)                                                                                          
            strINC_DESC = rng.Cells(rng.Row, 3)                                                                                         
            strPIR_Problem_Owner = FindCellValueByIncident(sourceSheet2, "PIR_Problem_Owner", strINC_NUM)                               
            strOR_CR_Root_Cause_Classification = FindCellValueByIncident(sourceSheet2, "OR_CR_Root_Cause_Classification", strINC_NUM)  
            strRoot_Cause_Owner = rng.Cells(rng.Row, 129)                                                                               
            strImpacted_Division = rng.Cells(rng.Row, 2)                                                                                
            strBusiness_Impact = rng.Cells() = FindCellValueByIncident(sourceSheet2, "App_Service_Impacts", strINC_NUM)  
            strIncident_Summary = rng.Cells() = FindCellValueByIncident(sourceSheet2, "MANCO_Summary", strINC_NUM)      
        End If
    Next

遇到的问题 #

rngFiltered_Range返回分组的不连续范围地址，例如：

$1:$1,$5:$7,$13:$16,$28:$28,$39:$39,$41:$41,$44:$44,$48:$48,$51:$51,$53:$53,$56:$56,$59:$59,$2585:$1048576

这种格式无法直接提取单个行号（比如$5:$7无法直接获取第6行），还会包含筛选后工作表底部的空行范围（如$2585:$1048576），导致遍历逻辑混乱。

解决方案 #

方法1：遍历可见区域的子区域（Areas） #

利用Range.Areas属性拆分不连续范围，逐个遍历每个子区域内的行，同时通过数据区域最后一行过滤空行范围：

Dim rngArea As Range
Dim rngRow As Range
Dim lastDataRow As Long

' 获取数据区域的最后一行（避免遍历到空行）
lastDataRow = sourceRange.Cells(sourceRange.Rows.Count, 1).End(xlUp).Row

Set rngFiltered_Range = sourceRange.Range("DB1").SpecialCells(xlCellTypeVisible).EntireRow

For Each rngArea In rngFiltered_Range.Areas
    ' 跳过超过数据最后一行的空行区域
    If rngArea.Row <= lastDataRow Then
        For Each rngRow In rngArea.Rows
            If rngRow.Row <> 1 Then ' 忽略表头行
                Debug.Print "当前行号：" & rngRow.Row
                ' 提取各列值，直接用rngRow.Cells(1, 列号)获取对应单元格值
                strPRIORITY3 = rngRow.Cells(1, 109).Value
                strINC_DATE = rngRow.Cells(1, 4).Value
                strINC_NUM = rngRow.Cells(1, 1).Value
                strINC_DESC = rngRow.Cells(1, 3).Value
                strPIR_Problem_Owner = FindCellValueByIncident(sourceSheet2, "PIR_Problem_Owner", strINC_NUM)
                strOR_CR_Root_Cause_Classification = FindCellValueByIncident(sourceSheet2, "OR_CR_Root_Cause_Classification", strINC_NUM)
                strRoot_Cause_Owner = rngRow.Cells(1, 129).Value
                strImpacted_Division = rngRow.Cells(1, 2).Value
                strBusiness_Impact = FindCellValueByIncident(sourceSheet2, "App_Service_Impacts", strINC_NUM)
                strIncident_Summary = FindCellValueByIncident(sourceSheet2, "MANCO_Summary", strINC_NUM)
            End If
        Next rngRow
    End If
Next rngArea

方法2：限定数据范围后获取可见行（更高效） #

直接从完整数据区域中获取可见行，避免包含工作表底部的空行：

Dim dataRange As Range
Dim visibleRows As Range
Dim rngRow As Range
Dim lastDataRow As Long

' 获取数据区域最后一行
lastDataRow = sourceRange.Cells(sourceRange.Rows.Count, 1).End(xlUp).Row
' 定义完整数据范围（假设数据从A1到DE列最后一行，可根据实际调整）
Set dataRange = sourceRange.Range("A1:DE" & lastDataRow)

' 获取数据区域内的可见行
Set visibleRows = dataRange.SpecialCells(xlCellTypeVisible)

For Each rngRow In visibleRows.Rows
    If rngRow.Row <> 1 Then ' 忽略表头行
        Debug.Print "当前行号：" & rngRow.Row
        ' 提取各列值
        strPRIORITY3 = rngRow.Cells(1, 109).Value
        ' ... 其他变量赋值逻辑
    End If
Next rngRow

这两种方法都能直接获取筛选后每行的行号，同时避开无效的空行范围，解决遍历和取值的问题。

内容的提问来源于stack exchange，提问作者Peter Lucas