VBA，Excel - 迭代过滤列中选择的单元格 [英] VBA, Excel - iterating through cells selected in filtered column

问题描述

在具有数据的工作表中，有一列具有应用的过滤器来限制显示的数据。用户选择列中的一个或多个单元（不一定是连续的）并执行VBA代码。
在VBA代码中，我想迭代选定的单元格并对它们进行一些操作，但是当只选择1个单元格（Excel中有效）时Excel行为有所不同。代码工作：

  Sub Macro1（）
如果Selection.count = 1然后
 counter = 1 
 Debug.Print Selection.Text 
 Else 
 counter = Selection.SpecialCells（xlCellTypeVisible）.count 
对于每个c在Selection.SpecialCells（xlCellTypeVisible）
 Debug.Print c.Text 
下一个c 
结束如果
 Debug.Print计数器
 End Sub 
  

QUESTION：
有没有办法，更优雅和干净的解决方案来做到这一点？要摆脱If-Then？

  Selection.SpecialCells（xlCellTypeVisible）.count 
  

如果只有一个单元格被激活（我认为Excel将选择扩展到整个工作表），就会产生溢出错误

  ActiveCell.Select 
 Selection.SpecialCells（xlCellTypeVisible）.count 
  

如果仅选择一个单元格（返回所选记录两次），则返回2

EDIT
请注意：过滤器由用户手动应用不是通过VBA代码。此外，用户手动从筛选视图中选择单元格，并在VBA代码中使用所选单元格。

解决方案

以下是基于此示例数据。

 列A列A列C 
abc 
 1 AA-01 BB-01 1 
 2 AAA-02 BBB-02 2 
 3 AAAA-03 BBBB-03 2 
  / pre> 
 
 
这些是我用于 AutoFilter方法。处理一个或多个可见行时没有任何麻烦，无需区分过滤器集。
  Sub filter_test（）
与Worksheets（Sheet16）'< ~~设置正确
如果.AutoFilterMode然后.AutoFilterMode = False 
带.Cells（1，1）.CurrentRegion 
 .AutoFilter字段：= 3，Criteria1：= 1 
'列A上的报告
带.Resize（.Rows.Count  -  1，1）.Offset（1，0）
如果CBool​​（Application.Subtotal（103，.Cells））然后
 reportVisibleCells visRng：=。Cells 
 Else 
 Debug.Printno visible cells with 1
 End If 
结束
 .AutoFilter字段：= 3 
 .AutoFilter字段：= 3，Criteria1：= 2 
'列B上的报表
 With .Resize（.Rows .Count  -  1，1）.Offset（1，1）
如果CBool​​（Applicat ion.Subtotal（103，.Cells））然后
 reportVisibleCells visRng：=。Cells 
 Else 
 Debug.Printno visible cells with 2
 End If 
结束
 .AutoFilter字段：= 3 
 .AutoFilter字段：= 3，Criteria1：= 3 
'列C 
带.Resize（.Rows.Count  -  1 ，1）.Offset（1，2）
如果CBool​​（Application.Subtotal（103，.Cells））然后
 reportVisibleCells visRng：=。Cells 
 Else 
 Debug.Print no visible cells with 3
 End If 
 End With 
 .AutoFilter field：= 3 
 End With 
 If .AutoFilterMode Then .AutoFilterMode = False 
结束
 End Sub 
 
子reportVisibleCells（visRng As Range）
 Dim vr As Range 
 
 With visRng.SpecialCells（xlCellTypeVisible）
 Fo r每个vr In .Cells 
 Debug.Print vr.Text 
下一个vr 
 Debug.Print .Count 
 End with 
 End Sub 
  
设置您的桌面，以便您可以看到工作表和VBE窗口。打开VBE的立即窗口（Ctrl + G），以便您可以看到 Debug.Print 报告。将光标放在 filter_test 子文件中，然后开始点击F8进行浏览。
 
 
 
 VBE的即时窗口的预期结果。
  AA-01 
 1 
 BBB-02 
 BBBB-03 
 2 
不可见单元格3 
  
 
In a worksheet with data there is a column with an applied filter to limit the displayed data. The user selects 1 or more cells (not necessarily continuous) in a column and execute a VBA code. 
In the VBA code I'd like to iterate through selected cells and do some operations with them, but there is difference in Excel behavior when only 1 cell is selected (active in Excel terms). Code that works:
Sub Macro1()
    If Selection.count = 1 Then
        counter = 1
        Debug.Print Selection.Text
    Else
        counter = Selection.SpecialCells(xlCellTypeVisible).count
        For Each c In Selection.SpecialCells(xlCellTypeVisible)
             Debug.Print c.Text
        Next c
    End If
    Debug.Print counter
End Sub
QUESTION:
Is there a way, more elegant and clean solution to do this? To get rid of If-Then?
Selection.SpecialCells(xlCellTypeVisible).count
generates overflow error if only one cell is activated (I think Excel expands selection to whole worksheet)
ActiveCell.Select
Selection.SpecialCells(xlCellTypeVisible).count
returns 2 if only one cell is selected (returns selected record twice)

EDIT
Please note: filter is applied manually by the user not by the VBA code. Also user manually selects cells from filtered view and that selected cells  are next used in VBA code.
解决方案

  
The following is based on this sample data.


     Column A   Column A   Column C
      a           b           c
1    AA-01       BB-01        1
2    AAA-02      BBB-02       2
3    AAAA-03     BBBB-03      2
These are the methods I use for the AutoFilter Method. I do not have any trouble dealing with one or more than one visible row and have no need to distinguish between the filter sets.
Sub filter_test()
    With Worksheets("Sheet16")  '<~~set this properly
        If .AutoFilterMode Then .AutoFilterMode = False
        With .Cells(1, 1).CurrentRegion
            .AutoFilter field:=3, Criteria1:=1
            'report on column A
            With .Resize(.Rows.Count - 1, 1).Offset(1, 0)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    reportVisibleCells visRng:=.Cells
                Else
                    Debug.Print "no visible cells with 1"
                End If
            End With
            .AutoFilter field:=3
            .AutoFilter field:=3, Criteria1:=2
            'report on column B
            With .Resize(.Rows.Count - 1, 1).Offset(1, 1)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    reportVisibleCells visRng:=.Cells
                Else
                    Debug.Print "no visible cells with 2"
                End If
            End With
            .AutoFilter field:=3
            .AutoFilter field:=3, Criteria1:=3
            'report on column C
            With .Resize(.Rows.Count - 1, 1).Offset(1, 2)
                If CBool(Application.Subtotal(103, .Cells)) Then
                    reportVisibleCells visRng:=.Cells
                Else
                    Debug.Print "no visible cells with 3"
                End If
            End With
            .AutoFilter field:=3
        End With
        If .AutoFilterMode Then .AutoFilterMode = False
    End With
End Sub

Sub reportVisibleCells(visRng As Range)
    Dim vr As Range

    With visRng.SpecialCells(xlCellTypeVisible)
        For Each vr In .Cells
            Debug.Print vr.Text
        Next vr
        Debug.Print .Count
    End With
End Sub
Set up your desktop so you can see both the worksheet and the VBE window. Open the VBE's Immediate window (Ctrl+G) so you can see the Debug.Print reporting. Put the cursor in the filter_test sub and start tapping F8 to walk through.

Expected results from the VBE's Immediate window.
AA-01
 1 
BBB-02
BBBB-03
 2 
no visible cells with 3


                        
这篇关于VBA，Excel  - 迭代过滤列中选择的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！