Excel VBA，从数组写入到列表对象，包括由过滤器隐藏的单元格

Question

我正在Excel 2013中工作，用VBA编写宏，并试图为list对象(表)列中的所有单元格指定一个值，包括一些可能被筛选器隐藏的单元格。为每个单元格分别分配一个值似乎大大降低了进程的速度，我发现了一个建议:将范围复制到一个变体数组中，遍历数组以更改值，然后将数组复制回范围。

varray = table.DataBodyRange.Columns(columnIndex).Value
For i = 1 to UBound(varray, 1)
    If (condition) then
        varray(i, 1) = i
    End If
Next i
table.DataBodyRange.Columns(columnIndex).Value = varray

在大多数情况下，这工作和解决了速度问题。当表中的任何列上有一个筛选器时，它就停止工作。据我所知，数组仍将正确地从表中复制，但不能正确地复制。筛选器隐藏的第一个单元之前的所有单元将被正确处理，但隐藏的单元将不会被更改，隐藏单元之后的任何单元将被设置为数组中的第一个值。

只有当单元格被筛选器隐藏时，才会发生这种情况，而不是当工作表的行被隐藏时。

有人知道为什么会这样吗？是否有一种方法，以正确地重新应用数组，尽管过滤器？如果没有，是否有一种方法可以暂时删除筛选器，进行更改，然后将相同的筛选器放回原处？还是一个不牺牲速度的更好的方法？

任何帮助都将不胜感激。谢谢!

Answer 1

我在另一个post 这里上找到了捕获自动筛选状态的代码。

Sub ReDoAutoFilter()
    Dim w As Worksheet
    Dim filterArray()
    Dim currentFiltRange As String
    Dim col As Integer

Set w = ActiveSheet

' Capture AutoFilter settings
With w.AutoFilter
    currentFiltRange = .Range.Address
    With .Filters
        ReDim filterArray(1 To .Count, 1 To 3)
        For f = 1 To .Count
            With .Item(f)
                If .On Then
                    filterArray(f, 1) = .Criteria1
                    If .Operator Then
                        filterArray(f, 2) = .Operator
                        filterArray(f, 3) = .Criteria2 'simply delete this line to make it work in Excel 2010
                    End If
                End If
            End With
        Next f
    End With
End With

'Remove AutoFilter
w.AutoFilterMode = False

' Your code here

' Restore Filter settings
For col = 1 To UBound(filterArray(), 1)
    If Not IsEmpty(filterArray(col, 1)) Then
        If filterArray(col, 2) Then
            w.Range(currentFiltRange).AutoFilter field:=col, _
            Criteria1:=filterArray(col, 1), _
            Operator:=filterArray(col, 2), _
            Criteria2:=filterArray(col, 3)
        Else
            w.Range(currentFiltRange).AutoFilter field:=col, _
            Criteria1:=filterArray(col, 1)
        End If
    End If
Next col
End Sub

一个已知的问题是，它无法找到捕获日期过滤器的方法，在这种情况下，标准是基于筛选器下拉列表中的Tree控件中的选择。