Excel 2013宏，仅显示基于一个单元格值的特定行 [英] Excel 2013 macro to show only specific rows based on one cell value

问题描述

我很喜欢excel宏和vba。
我有一个excel文件，第一页中有大约30万行，第一列中有项目标识符（它们可能是几个具有相同值），第二个表中约有1000行（第一列还包含项标识符但它们在这里是独一无二的）。我需要编写一个基于第二张表将第一张表中的行隐藏的宏。我的意思是我需要循环抛出第一张表中的所有行，如果第一个单元格值与第二个列的第一列的任何单元格不匹配，则隐藏此行。

i知道这将非常慢，因为每次我需要比较cellvalue与另外1000个单元格值，我有30万行。我该怎么做？你可以建议最快的方式吗？任何帮助将不胜感激，谢谢提前。

EDIT
搜索很多我自己的宏

  Sub hide（）
 Dim MyCell，Rng As Range，Rn2 As Range 
 Dim MyCell2 
 Dim id（1 To 1392）As String 
设置Rng =表（Sheet0）范围（C162403：C339579）
设置Rng2 =表（IT东西）范围（A1：A22031）
i = 1 
每个MyCell2在Rng2 
如果不是MyCell2.EntireRow.Hidden然后
 id（i）= MyCell2.Value 
i = i + 1 
结束If 
 Next MyCell2 
j = 0 
对于每个MyCell在Rng 
对于A = 1到1392 
如果MyCell = id（A）然后
j = 1 
结束如果
下一个A 
如果j = 0然后
 MyCell.EntireRow.Hidden = True 
 ElseIf j = 1然后
j = 0 
结束If 
 Next MyCell 
 End Sub 
  

它正在处理我的excel文件，但是这是非常慢的...我可以改善吗？

解决方案

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

hth

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.

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.

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??

解决方案

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...

hth

这篇关于Excel 2013宏，仅显示基于一个单元格值的特定行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！