M
Matthew Dyer
There's got to be a faster way to do this, so I come to the brain trust asking for brainy guidance!
I am comparing two ranges, each with appx 7000 items in them. I want to seeif an item in list1 is also in list2. If it is, i want to hide that row. Right now, I am going through list1, item by item, and comparing it to list2item by item via a double loop to achieve this. The problem is that this results in appx 40,320,350 individual iterations of my loop, thus slowing things down considerably. Even with screen updating turned off this is takingquite a bit of time. Is there a way to make this run faster? below is my code:
Sub compare()
Application.ScreenUpdating = False
Dim todaycell As Range
Dim yesterdaycell As Range
For i = 1 To lastrow(Sheet9)
Set yesterdaycell = Sheet9.Range("a" & i)
For ii = 1 To lastrow(Sheet10)
Set todaycell = Sheet10.Range("a" & ii)
If yesterdaycell.Value = todaycell.Value Then
yesterdaycell.EntireRow.Hidden = True
End If
Next ii
Next i
Application.ScreenUpdating = True
End Sub
I am comparing two ranges, each with appx 7000 items in them. I want to seeif an item in list1 is also in list2. If it is, i want to hide that row. Right now, I am going through list1, item by item, and comparing it to list2item by item via a double loop to achieve this. The problem is that this results in appx 40,320,350 individual iterations of my loop, thus slowing things down considerably. Even with screen updating turned off this is takingquite a bit of time. Is there a way to make this run faster? below is my code:
Sub compare()
Application.ScreenUpdating = False
Dim todaycell As Range
Dim yesterdaycell As Range
For i = 1 To lastrow(Sheet9)
Set yesterdaycell = Sheet9.Range("a" & i)
For ii = 1 To lastrow(Sheet10)
Set todaycell = Sheet10.Range("a" & ii)
If yesterdaycell.Value = todaycell.Value Then
yesterdaycell.EntireRow.Hidden = True
End If
Next ii
Next i
Application.ScreenUpdating = True
End Sub