highlight duplicate rows with fixed range

Re: highlight duplicate rows with fixed range

Hi Sholtan,

Try this macro which is almost an exact copy of the code I gave you from this thread :confused:

Option ExplicitSub Macro1()
     
    Const lngStartRow As Long = 1 'Starting Row number for the data. Change to suit.
    
    Dim lngLastRow    As Long
    Dim lngMyRow      As Long
    Dim xlnCalcMethod As XlCalculation
     
    With Application
        xlnCalcMethod = .Calculation 'Store current calculation method
        .Calculation = xlCalculationManual 'Set calculation method to manual
        .ScreenUpdating = False
    End With
     
    'Note the sheet must have data in it or the next lines will error out!!
    lngLastRow = Range("B:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
          
    'Copy Primary Key formula down dataset
    Range(Cells(lngStartRow, "G"), Cells(lngLastRow, "G")).Formula = "=B1&F1"
     
    'Loop through Primary Key field and highlight duplicates
    For lngMyRow = lngStartRow To lngLastRow
        If Evaluate("COUNTIF(" & Range("G" & lngStartRow & ":G" & lngStartRow & lngLastRow).Address(True, True) & "," & Range("G" & lngMyRow).Address(False, True) & ")") > 1 Then
            Range(Cells(lngMyRow, "B"), Cells(lngMyRow, "F")).Interior.Color = RGB(255, 255, 0) 'Highlight duplicates in yellow. Change to suit.
        End If
    Next lngMyRow
     
    'Delete helper column
    Columns("G").EntireColumn.Delete
     
    With Application
        .Calculation = xlnCalcMethod 'Set calculation method to its original setting
        .ScreenUpdating = True
    End With
     
    MsgBox "Any duplicates have now been highlighted.", vbInformation
     
End Sub

Robert

Re: highlight duplicate rows with fixed range

That's a hard question to answer as from Excel 2007 there are so many rows (1,048,576) and columns (16,384) on every tab that a workbook can get bogged down pretty quick on any machine.

Re: highlight duplicate rows with fixed range

I'm not a hardware expert so I will decline to offer advice as it may not be correct and / or relevant. Thanks.

Re: highlight duplicate rows with fixed range

Not sure as it works perfect on the workbook you posted :confused:

Post the work you're using and I'll have another look.

Re: highlight duplicate rows with fixed range

You'll need to post the workbook you're using so either I or anyone reading this thread will be able to help.

Re: highlight duplicate rows with fixed range

I will have to have a look tomorrow as it's a bit late in Aus now.

Re: highlight duplicate rows with fixed range

I'm confused - in the first workbook you posted "My row range is fixed B1 TO F1 ,B2 TO F2 AND SO ON SO don’t compare the complete row to each other" so my code only highlights rows where B1 and F1 is duplicated which in the example you posted was with B3 and F3 and you posted sample data across only 5 columns. The second workbook you posted has 8,996 columns. If you want to know which row is duplicated based on each of these 8,996 columns I can't help as I couldn't create a primary key for that many fields.

Re: highlight duplicate rows with fixed range

Quote

if you want to know which row is duplicated based on each of these 8,996 columns i can't help as i couldn't create a primary key for that many fields.

Excel can't handle a formula to join the 8,996 columns needed for my original code (from this post) to work!! There is nothing I can think of to solve your issue. Good luck with it.