Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting Duplicates in a List
I'm using the following code to highlight duplicate data
contained in two lists. Unfortunately both lists are huge so it's taking the macro a long time to run and is using a lot of memory. Would anyone have any advice on how the code could be modified to run more efficiently? My thought was that maybe the data could be loaded into an array but I am not yet proficient with how to handle VBA arrays. Thanks for your help. Sub FindDuplicates() Application.ScreenUpdating = False Dim Rng1 As Range Dim Rng2 As Range Dim x As Variant Dim y As Variant Set Rng1 = Range("B7:B15000") Set Rng2 = Range("C7:C15000") For Each x In Rng1 For Each y In Rng2 If x = y Then x.Interior.ColorIndex = 6 y.Interior.ColorIndex = 6 End If Next y Next x Application.ScreenUpdating = True End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting Duplicates in a List
Have you tried conditional formatting instead?
Format--Conditional Format--Formula is: =IF(COUNTIF($C$7:$C$15000, B7)1,TRUE,FALSE) John Mansfield wrote: I'm using the following code to highlight duplicate data contained in two lists. Unfortunately both lists are huge so it's taking the macro a long time to run and is using a lot of memory. Would anyone have any advice on how the code could be modified to run more efficiently? My thought was that maybe the data could be loaded into an array but I am not yet proficient with how to handle VBA arrays. Thanks for your help. Sub FindDuplicates() Application.ScreenUpdating = False Dim Rng1 As Range Dim Rng2 As Range Dim x As Variant Dim y As Variant Set Rng1 = Range("B7:B15000") Set Rng2 = Range("C7:C15000") For Each x In Rng1 For Each y In Rng2 If x = y Then x.Interior.ColorIndex = 6 y.Interior.ColorIndex = 6 End If Next y Next x Application.ScreenUpdating = True End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlighting Duplicates in a List
Make that 0 since you are looking for duplicates in different columns.
JWolf wrote: Have you tried conditional formatting instead? Format--Conditional Format--Formula is: =IF(COUNTIF($C$7:$C$15000, B7)1,TRUE,FALSE) John Mansfield wrote: I'm using the following code to highlight duplicate data contained in two lists. Unfortunately both lists are huge so it's taking the macro a long time to run and is using a lot of memory. Would anyone have any advice on how the code could be modified to run more efficiently? My thought was that maybe the data could be loaded into an array but I am not yet proficient with how to handle VBA arrays. Thanks for your help. Sub FindDuplicates() Application.ScreenUpdating = False Dim Rng1 As Range Dim Rng2 As Range Dim x As Variant Dim y As Variant Set Rng1 = Range("B7:B15000") Set Rng2 = Range("C7:C15000") For Each x In Rng1 For Each y In Rng2 If x = y Then x.Interior.ColorIndex = 6 y.Interior.ColorIndex = 6 End If Next y Next x Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
highlighting duplicates...except it doesn't | Excel Discussion (Misc queries) | |||
Need Help with Highlighting duplicates across several sheets | Excel Worksheet Functions | |||
highlighting duplicates | Excel Worksheet Functions | |||
Help with Highlighting all duplicates in a row | Excel Discussion (Misc queries) | |||
Highlighting Duplicates | New Users to Excel |