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