Оптимизировать для каждого цикла в сводной таблице

Я с трудом пытаюсь оптимизировать VBA For Each цикл, который должен анализировать 10-15 тыс. строк в сводной таблице, основанной на таблице Excel, все в одной книге. В настоящее время это занимает около 2 минут, что я хотел бы улучшить, поскольку я использую это на собраниях.

Я искал и нашел несколько интересных предложений, таких как отключение обновления экрана, ручные вычисления иdim"Мои переменные, как и другие типы данных, но я не получил разницы в скорости. Я предполагаю, For Each Цикл просто не предназначен для этой цели.

Читая этот пост Супер пользователя, похоже, я мог бы поместить соответствующие значения в "Словарь" и это было бы очень быстро. Тот факт, что у меня есть два элемента для фильтрации, делает его немного более сложным для меня.

Я "учусь на собственном опыте" и не претендую на звание программиста VBA, так что любая помощь очень ценится!

Dim pvtTable As PivotTable
Dim pvtField1, pvtField2 As PivotField
Dim pvtItem1, pvtItem2 As PivotItem

Set ws = ActiveSheet
Set pvtTable = ws.PivotTables("PTReport")
Set pvtField1 = pvtTable.PivotFields("callNummer")
Set pvtField2 = pvtTable.PivotFields("Destination")

Application.ScreenUpdating = False

For Each pvtItem1 In pvtField1.PivotItems
  If InStr(UCase(pvtItem1), "STORE") > 0 Then
    pvtItem1.Visible = True
  Else
    pvtItem1.Visible = False
  End If
Next

For Each pvtItem2 In pvtField2.PivotItems
  If InStr(UCase(pvtItem2), "221") > 0 Then
    pvtItem2.Visible = True
  Else
    pvtItem2.Visible = False
  End If
Next

Application.ScreenUpdating = True

Если я могу что-то уточнить или предоставить, более подробную информацию, пожалуйста, дайте мне знать.

Источник

2 ответа

Решение

Я подозреваю, что звонки на 20-30К InStr(UCase()) проблема Я предлагаю вам определить пару вспомогательных столбцов. Например, если ваши данные "callNummer" и "Destination" находятся в столбцах A а также B, задавать

  • Y1 → =IFERROR(SEARCH("store", A1), 0)
  • Z1 → =IFERROR(SEARCH("221", B1), 0)

а затем измените код VBA, чтобы проверить, Yn а также Zn являются > 0, Таким образом, проверка строки в строке выполняется всякий раз, когда изменяются данные "callNummer" и "Destination", и для подпрограммы VBA требуется гораздо меньше работы. И, конечно же, вы можете скрыть вспомогательные столбцы, когда все заработает.

Я не уверен, как именно перевести ваш pvtItemN.Visible = … заявления в этой структуре.

Но даже если вы не можете заставить это работать, вы можете изменить InStr(UCase(pvtItem2), "221") тест на InStr(pvtItem2, "221"), Если все, что вам нужно, это число, то нет смысла переводить алфавитное содержимое ячейки в верхний регистр.

Источник

Перед первым циклом for...next добавьте следующий код:

Dim saveCalc as xlCalculation

With Application
   .ScreenUpdating = False
   saveCalc = .Calculation
   .Calculation = xlCalculationManual
End With

Затем, после второго цикла for...next, добавьте следующий код:

With Application
    .ScreenUpdating = True
    .Calculation = saveCalc
End With
Источник
Другие вопросы по тегам