MS Excel, how to create a macro to find duplicates and select them?

Question

How to create a macro in MS excel to find duplicates in a spreadsheet and select it

Answer 1

You do not need a VBA macro. You can just use conditional formatting. Microsoft explains how to do what you think is here:

http://office.microsoft.com/en-us/excel/HA011366161033.aspx

If you really need a macro, the easiest way is to write down the steps described above and then edit as necessary.

Answer 2

Perhaps this snippet is useful:

Public Sub MarkDuplicates()
Dim iWarnColor As Integer
Dim rng As Range
Dim rngCell As Variant


Set rng = Range("A1:A200") ' area to check '
iWarnColor = xlThemeColorAccent2

For Each rngCell In rng.Cells
    vVal = rngCell.Text
    If (WorksheetFunction.CountIf(rng, vVal) = 1) Then
        rngCell.Interior.Pattern = xlNone
    Else
        rngCell.Interior.ColorIndex = iWarnColor
    End If
Next rngCell
End Sub

Answer 3

Sub MarkDuplicates2()
Dim rngCell As Variant
Dim flag As Integer

Dim LastRow As Long

'To Check Duplicate records for dynamic rows:
    LastRow = 0
    With ActiveSheet
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
    End With

flag = 0`enter code here`
'Cell(2,2) represent "B2"
Set rng = Range(Cells(2, 2), Cells(LastRow, 2))
iWarnColor = xlThemeColorAccent2

For Each rngCell In rng.Cells
    vVal = rngCell.Text
    If (WorksheetFunction.CountIf(rng, vVal) = 1) Then
        rngCell.Interior.Pattern = xlNone

    Else
        rngCell.Interior.ColorIndex = iWarnColor
        flag = flag + 1
    End If
Next rngCell

    If flag > 0 Then
    MsgBox flag & " cells (in light blue) contain an error.  Please Check!"
    Else
    MsgBox " Data Validation completed. No errors found."
    End If

End Sub

Answer 4

Sub Macro1()

    Dim Counter As Integer

    For Counter = 1 To 35

        'Cells.(X,Y) X = number, Y = Letter i.e D5 Cells(5,4)
        firstValue = ActiveSheet.Cells(Counter, 3)
        SecondValue = ActiveSheet.Cells(Counter, 4)

        If firstValue = SecondValue Then
            Rows(Counter).Interior.Color = RGB(255, 10, 10)
        End If


    Next

End Sub

Answer 5

: excel

: " " , ( "" ). ( ) ( ). "

, . VBA script. Excel .

, 2010 , . - , .