Excel VBA, запись из массива в объект списка, включая ячейки, скрытые фильтрами

Я работаю в Excel 2013, пишу макрос в VBA и пытаюсь присвоить значение всем ячейкам в столбце объекта списка (таблицы), включая те, которые могут быть скрыты фильтрами. Назначение значения каждой ячейке в отдельности, казалось, значительно замедляло процесс, и я нашел предложение скопировать диапазон в вариантный массив, выполнить итерацию по массиву, чтобы изменить значения, а затем скопировать массив обратно в диапазон.

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

По большей части это работает и решает проблему скорости. Он перестает работать, когда есть фильтр для любого столбца в таблице. Насколько я могу судить, массив все равно копируется из таблицы правильно, но не копируется обратно корректно. Все ячейки до первой ячейки, скрытой фильтром, будут обработаны правильно, но скрытые ячейки не будут изменены, а для всех ячеек после скрытых ячеек будет установлено первое значение в массиве.

Это происходит только тогда, когда ячейки скрыты фильтром, а не когда скрыты строки рабочего листа.

кто-нибудь знает, почему это произошло? Есть ли способ правильно повторно применить массив, несмотря на фильтр? Если нет, есть ли способ временно удалить фильтр, внести изменения, а затем вернуть тот же фильтр на место? Или просто лучший способ сделать это, не жертвуя скоростью?

Любая помощь будет оценена по достоинству. Спасибо!


arrays excel vba autofilter listobject
person SudoNim    schedule 19.08.2016    source источник
comment
Вы можете удалить фильтр и применить его обратно, когда закончите? Кроме того, попробуйте stackoverflow .com/documentation/excel-vba/1107/   -  person Slai    schedule 20.08.2016
comment
Я рассматривал возможность удаления фильтра и его повторного применения, но не знаю, как получить информацию о текущем фильтре. И у меня уже было отключено обновление экрана и вычисление, и я просто попробовал это с отключенными событиями, и это не намного быстрее.   -  person SudoNim    schedule 20.08.2016
comment
Этот вопрос не является дубликатом этого. Актуальной проблемой является ошибка Excel, приводящая к некорректным результатам установки данных в отфильтрованный диапазон. Есть эффективный обходной путь: запись массива вариантов во временный нефильтрованный диапазон, а затем запись данных в целевой диапазон из последнего. См. Excel 2013 VBA Ошибка при копировании массива вариантов в диапазон ячеек в отфильтрованной таблице.   -  person Aleksey F.    schedule 12.09.2019

Ответы (1)


arrow_upward
0
arrow_downward

Я нашел код в другом сообщении здесь, который фиксирует состояние автофильтра.

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

Известная проблема заключается в том, что он не может найти способ захвата фильтров даты, когда критерии основаны на выборе из элемента управления «Дерево» в раскрывающемся списке «Фильтр».

person ChrisB    schedule 19.08.2016
comment
Спасибо! Я думаю, что этот обходной путь сработает для меня, но я хотел бы знать, что пошло не так в первую очередь - person SudoNim; 20.08.2016