Запись данных массив<->Excel

[andbrother]

Добрый день. Искал на разных форумах, но не нашел (либо не понял, что это оно).
Требуется большой объем данных записать в двухмерных массив, поработать с ним, и результат выгрузить на другой лист. Сейчас делаю в лоб - обращаясь к каждой ячейке через Cells либо Range. И если скорость чтения из ячеек и запись в массив проходит достаточно быстро, то вот запись из массива в ячейки - недопустимо долго. Application.ScreenUpdating отключаю.
Какие есть быстрые способы копирования? Самый маленький блок данных - 1500 строк на 15 столбцов, максимальный - в пределах 220 тыс. строк.

Dim F(1500, 20) As Double
For i = 1 To 1500
        For j = 1 To 20
            F(i, j) = Cells(i, j)
        Next j
Next i
.............................
For i = 1 To 1500
        For j = 1 To 20
            Cells(i, j) = F(i, j)
        Next j
Next i

И еще маленький вопрос - какой метод быстрее, Cells(i,j) или Range("A"+Format(i))?

[Hugo121]

быстрее

f=[a1:o1500]

Ну или не лениво:

Sub tt()
    Dim f()
    f = Range("A1:O1500").Value
    f(5, 10) = "test"
    Range("A1:O1500").Value = f
End Sub

[andbrother]

Спасибо большое. Одно уточнение - возможно из приведенного дин. массива записать только определенный столбец? Например, в Range("B1:B1500") записать, столбец 5 массива f?

[Hugo121]

Первый (или сколько нужно с начала) можно, отрубив лишнее с помощью Resize (указать, сколько столбцов выгружаете).
Ну а если нужно только пятый - так делайте из пятого отдельный параллельный массив, его и выгружайте.
ну или перебором переложите нужные данные в другой созданный массив размером на один столбец, его и выгружайте - не бойтесь, в массивах перебор быстрый.

[andbrother]

Спасибо, про отдельный массив была мысль, но связываться с дополнительными массивами без нужды не хотелось.

Разрешите последний вопрос, правда не по массивам. Из примерно десятка разных макросов, что пользуюсь в данный момент, только у одного есть проблема повторного запуска - с каждым запуском все медленнее и медленнее работает. После 5-6 запуска скорость падает с 5-6 сек. до двух-трех минут. Erase для массивов стоит (хотя массивы не слишком большие). По диспетчеру задач память не забивается. Существует какая-нибудь функция полной очистки памяти по завершению макроса?

[Hugo121]

По окончании кода переменные должны умирать, если они не публичные. Спецкоманды я не знаю (erase или set объект=nothing это само собой, можно ещё текстовые переменные очищать s = Empty).
Посмотрите - в том коде не используются публичные переменные? Что в переменных накапливается, если код прогонять пошагово?
Может быть там нарастает диапазон, а он перебирается в union, часто добавляя части диапазона? (union с каждым шагом заполняется всё медленнее и медленнее).

[andbrother]

Из публичных только имена листов книг Dim NameSheets(10) As String
Range на накапливается нигде.
Прогонял с включенным автообновлением - тормозит на участке кода удаления строк:

For i = 6 To kolich
    If Cells(i, 3) = "" Then GoTo Exit1
    If Cells(i, 2) = Cells(i - 1, 2) And Cells(i, 3) = Cells(i - 1, 3) _
    And Cells(i, 4) = Cells(i - 1, 4) Then
        For j = 5 To 16
            Cells(i - 1, j) = tmp + Cells(i, j)
        Next j
        Rows(Format(i)).Select
        Selection.Delete Shift:=xlUp
        i = i - 1
    End If
Next i

Я понимаю, код долгий сам по себе, но именно он при повторных запусках работает все медленнее и медленнее. Пересмотрел код - в память, кстати, особо ничего и не записывается, все на уровне команды Cells выполняется. Непонятный момент.

[Hugo121]

Может быть пишется история изменений файла - а там при удалении строк ещё и куча формул может быть корректируется.
И я бы удалял циклом снизу вверх.
Но вообще нужно видеть пример в файле - кажется, тут можно сделать быстро на словаре и массиве.
Зачем вообще УДАЛЯТЬ строки?

[andbrother]

Про словарь - тут на форуме видел темы про него, но пока не разбирался, что это такое и как работает. Формул на листе нет вообще. История изменений - вполне возможно, не сталкивался раньше.

На массиве точно можно сделать. Просто сам факт замедления работы подозрителен. Заинтересовал прямо. А почему удаление строк - в далеком 2009 я не придумал ничего более простого в написании кода, чем приплюсовывать строки одна к другой и удалять их. А нет ничего постоянней временного

Спасибо за помощь, не смею больше надоедать.

[SAS888]

Одно уточнение - возможно из приведенного дин. массива записать только определенный столбец? Например, в Range("B1:B1500") записать, столбец 5 массива f?

Возможно. Например, сформируем массив f из диапазона ячеек активного листа и 5-й столбец этого массива выгрузим в столбец "B" 2-го листа:

Sub qq()
    f = [A1:O1500].Value
    Sheets(2).[B1].Resize(UBound(f, 1)).Value = Application.Index(f, 0, 5)
End Sub