vba Excel 2013 宏根据一个单元格值仅显示特定行

Excel 2013 macro to show only specific rows based on one cell value

提问by Asiat

i am new to excel macros and vba. I have an excel file with about 300000 rows in first sheet where there are item identifiers in first column(they might be several which has the same value), and about 1000 rows in second sheet(first column also contains item identifiers but they are unique here). i need to write a macro that hides rows in first sheet based on the second sheet. i mean i need to loop throw all rows in first sheet and if first cell value does not match any cell of first column of the second sheet then hide this row.

我是 excel 宏和 vba 的新手。我有一个 excel 文件，第一张表中有大约 300000 行，其中第一列中有项目标识符（它们可能有几个具有相同的值），第二张表中有大约 1000 行（第一列也包含项目标识符，但它们是唯一的这里）。我需要编写一个宏，根据第二张表隐藏第一张表中的行。我的意思是我需要循环抛出第一个工作表中的所有行，如果第一个单元格值与第二个工作表第一列的任何单元格不匹配，则隐藏该行。

i know that it will be very slow as everytime i need to compare cellvalue with another 1000 cell values, and i have 300 000 rows. How can i do it? could you please suggest the fastest way? any help would be appreciated, thanks in advance.

我知道它会很慢，因为每次我需要将 cellvalue 与另外 1000 个单元格值进行比较时，我有 300 000 行。我该怎么做？你能建议最快的方法吗？任何帮助将不胜感激，提前致谢。

EDIT after searching a lot i made my own macro

搜索了很多后编辑我做了我自己的宏

Sub hide()    
Dim MyCell, Rng As Range, Rn2 As Range
Dim MyCell2
Dim id(1 To 1392) As String
Set Rng = Sheets("Sheet0").Range("C162403:C339579")
Set Rng2 = Sheets("IT stuff").Range("A1:A22031")
i = 1
For Each MyCell2 In Rng2
    If Not MyCell2.EntireRow.Hidden Then
        id(i) = MyCell2.Value
        i = i + 1
    End If
Next MyCell2
j = 0
For Each MyCell In Rng
    For A = 1 To 1392
        If MyCell = id(A) Then
        j = 1
        End If
    Next A
    If j = 0 Then
        MyCell.EntireRow.Hidden = True
    ElseIf j = 1 Then
        j = 0
    End If
Next MyCell
End Sub

it is processing now my excel file, however it is very slow... how can i improve it??

它现在正在处理我的 excel 文件，但是它很慢......我该如何改进它？

采纳答案by Pat Mustard

Making calls to the Excel object model slows things considerably so it's probably best to load the values you want to check for into a dictionary or array and reference that instead. You could also load the row number and value of the rows you are checking in another dictionary and cross reference the two data structures while making note of the rows you need to hide. Working this way will take up quite a bit of memory but will definitely be faster than cross referencing sheets directly...

调用 Excel 对象模型会显着减慢速度，因此最好将要检查的值加载到字典或数组中并改为引用它们。您还可以在另一个字典中加载您正在检查的行的行号和值，并交叉引用这两个数据结构，同时记下您需要隐藏的行。以这种方式工作将占用相当多的内存，但肯定会比直接交叉引用工作表更快......

hth

第

回答by Siddharth Rout

Why VBA? And not Excel Formula(Vlookup)+ Autofilter

为什么是 VBA？而不是Excel Formula(Vlookup)+Autofilter

Let's say your Sheet 1 looks like this

假设您的工作表 1 看起来像这样

And sheet 2 looks like this

表 2 看起来像这样

Simply Add a Column as shown below and put the formula and then use Autofilter to Hide the relevant rows.

只需添加如下所示的列并输入公式，然后使用自动过滤器隐藏相关行。

Formula used in I2is

中使用的公式I2是

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

回答by chuff

The following code takes a somewhat different approach to your problem. You will note that it assumes that Sheet1 has a set of values in column A plus an unspecified number of data columns and that Sheet2 has only a a set of values in column A against which the Sheet1 column A values are matched.

以下代码对您的问题采用了不同的方法。您会注意到，它假定 Sheet1 在 A 列中具有一组值以及未指定数量的数据列，并且 Sheet2 在 A 列中只有一组与 Sheet1 A 列值相匹配的值。

The code does the following:

该代码执行以下操作：

• Creates match values in the column to the right of the last data column in worksheet 1 (1 = no-match, 0 = match)
• Sets an autofilter on the sheet 1 data range, with a criterion value of 1 on the match column (i.e., filter to show only no-matches)
• Assigns the filtered rows to a range variable
• Removes the filter and clears the match column
• Hides in bulk the rows identified in the range variable

• 在工作表 1 中最后一个数据列右侧的列中创建匹配值（1 = 不匹配，0 = 匹配）
• 在工作表 1 数据范围上设置自动过滤器，匹配列上的标准值为 1（即过滤器以仅显示不匹配项）
• 将过滤后的行分配给范围变量
• 移除过滤器并清除匹配列
• 批量隐藏范围变量中标识的行

I tested the procedure with a Sheet1 dataset of 300,000 rows of code values in column A and random numeric data in columns B and C, with just over 1,000 match values in Sheet2. The randomly generated 10-character code and match values were constructed so that 20 percent of the Sheet1 column A values were non-matches.

我使用包含 A 列中 300,000 行代码值和 B 列和 C 列中的随机数字数据的 Sheet1 数据集测试了该过程，而 Sheet2 中的匹配值仅超过 1,000 个。随机生成的 10 个字符的代码和匹配值的构造使得 Sheet1 列 A 值的 20% 是不匹配的。

Run times against these data averaged under two minutes.

针对这些数据的运行时间平均不到两分钟。

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