Макрос Excel 2013 для отображения только определенных строк на основе одного значения ячейки

Выполнение вызовов объектной модели Excel значительно замедляет работу, поэтому, вероятно, лучше всего загрузить значения, которые вы хотите проверить, в словарь или массив и вместо этого ссылаться на них. Вы также можете загрузить номер строки и значение строк, которые вы проверяете, в другом словаре и перекрестно ссылаться на две структуры данных, отмечая строки, которые вам нужно скрыть. Работа таким образом займет довольно много памяти, но определенно будет быстрее, чем прямая перекрестная ссылка на листы ...

hth

Почему именно VBA? А не Excel Formula(Vlookup) + Autofilter

Допустим, ваш Лист 1 выглядит так

А лист 2 выглядит так

Просто добавьте столбец, как показано ниже, и введите формулу, а затем используйте автофильтр, чтобы скрыть соответствующие строки.

Формула, использованная в I2:

=IF(ISERROR(VLOOKUP(A2,Sheet2!A:A,1,0)),"","True")

В следующем коде используется несколько иной подход к вашей проблеме. Вы заметите, что он предполагает, что Sheet1 имеет набор значений в столбце A плюс неопределенное количество столбцов данных и что Sheet2 имеет только набор значений в столбце A, с которым сопоставляются значения столбца A Sheet1.

Код делает следующее:

• Создает совпадающие значения в столбце справа от последнего столбца данных на листе 1 (1 = нет совпадений, 0 = совпадений)
• Устанавливает автофильтр для диапазона данных листа 1 со значением критерия 1 в столбце соответствия (т. Е. Фильтр для отображения только несоответствий)
• Назначает отфильтрованные строки переменной диапазона
• Удаляет фильтр и очищает столбец соответствия
• Массовое скрытие строк, указанных в переменной диапазона

Я протестировал процедуру с набором данных Sheet1, состоящим из 300 000 строк кодовых значений в столбце A и случайных числовых данных в столбцах B и C, с немногим более 1000 значений соответствия в Sheet2. Произвольно сгенерированный 10-значный код и значения соответствия были построены таким образом, что 20 процентов значений столбца A Sheet1 не совпадали.

Среднее время работы с этими данными составляет менее двух минут.

Sub MatchFilterAndHide2()

    Dim calc As Variant
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim ws1Name As String, ws2Name As String
    Dim rng1 As Range, rng2 As Range
    Dim hideRng As Range
    Dim lastRow1 As Long, lastRow2 As Long
    Dim lastCol1 As Long

    Application.ScreenUpdating = False
    calc = Application.Calculation
    Application.Calculation = xlCalculationManual

    ws1Name = "Sheet1"
    Set ws1 = Worksheets(ws1Name)
    With ws1
        lastRow1 = .Range("A" & .Rows.Count).End(xlUp).Row
        lastCol1 = .Cells(1, ws1.Columns.Count).End(xlToLeft).Column + 1
        Set rng1 = .Range(.Cells(1, 1), .Cells(lastRow1, lastCol1))
    End With

    ws2Name = "Sheet2"
    Set ws2 = Worksheets(ws2Name)
    With ws2
        lastRow2 = .Range("A" & .Rows.Count).End(xlUp).Row
        Set rng2 = .Range("A2:A" & lastRow2)
    End With

    'add column of match values one column to the right of last data column
    '1 = no-match, 0 = match
    With ws1.Range(ws1.Cells(2, lastCol1), ws1.Cells(lastRow1, lastCol1))
        .FormulaArray = "=N(ISNA(MATCH(" & ws1Name & "!" & rng1.Address & _
            "," & ws2Name & "!" & rng2.Address & ",0)))"
        .Value = .Value
    End With

    'set autofilter on rng1 and filter to show the no-matches
    With ws1.Range(ws1.Cells(1, 1), ws1.Cells(1, lastCol1))
        .AutoFilter
        .AutoFilter field:=lastCol1, Criteria1:=1
    End With

    With ws1
        'assign no-matches to range object
        Set hideRng = .Range("A2:A" & lastRow1).SpecialCells(xlCellTypeVisible)

        'turn off autofilter, clear match column, and hide no-matches
        .AutoFilterMode = False
        .Cells(1, lastCol1).EntireColumn.Clear
        hideRng.EntireRow.Hidden = True
        .Cells(1, 1).Select
    End With

    Application.Calculation = calc
    Application.ScreenUpdating = True
End Sub