When a conditional formatting rule is overkill, use these VBA procedures to spotlight duplicates after which take away the highlighting in Microsoft Excel.

Picture: Rawpixel.com/Shutterstock

There are numerous methods to seek out duplicates in a Microsoft Excel knowledge vary—conditional formatting being one of the vital generally used. When the rule is TRUE, the format stays till the situation is FALSE otherwise you take away the rule. Nonetheless, generally, you solely need a fast look or depend of duplicates; you don’t need a everlasting format. Or, extra moderately, customers may not be savvy sufficient to use a conditional formatting rule. In both case, this text will assessment VBA code that highlights duplicates in an information vary. This text additionally consists of code to take away highlighting from an information vary.

SEE: 83 Excel suggestions each person ought to grasp (TechRepublic)

I am utilizing Microsoft 365 on a Home windows 10 64-bit system, however you should use earlier variations. Use the downloadable .xlsm, .xls, or .cls information to acquire the code. Excel On-line would not help macros. This text assumes that you’ve got primary Excel expertise; nevertheless, even a newbie ought to be capable of observe the directions to success.

How one can use the VBA process

Earlier than we assessment the procedures, it is value noting that code-wise, the 2 procedures are nearly equivalent. You can mix the 2 into one process and immediate the person to establish whether or not the duty is to spotlight or take away highlighting, however that requires an additional click on each time you run the process. Working two eliminates that further click on. However some would select to mix them, so it is value contemplating.

Itemizing A highlights duplicate values in an information vary. After declaring and defining variables, the code prompts the person to pick out a spread. After checks on that choice, the code highlights duplicates after which shows the variety of duplicates highlighted. Itemizing B is analogous however removes highlights and would not show the duplicate depend.

HighlightDuplicates() units a spotlight by setting the ColorIndex property to 36, which is yellow; Itemizing B units the identical setting to 0, which isn’t any fill coloration in any respect. If the vary has a everlasting fill coloration, you may wish to accommodate that in Itemizing B through the use of that very same fill coloration setting as an alternative of 0.

Itemizing A

Sub HighlightDuplicates()

‘Spotlight duplicates in chosen vary.

Dim selRange As Vary

Dim curCell As Vary

Dim j As Integer

‘Immediate person to pick out vary.

‘Will need to have Break on Unhandled Errors chosen.

On Error Resume Subsequent

Set selRange = Software.InputBox( _

    Title:=”Choose vary”, _

    Immediate:=”Choose a spread to test for duplicate values.”, _

    Sort:=8)

On Error GoTo 0

‘Check for cancel.

If selRange Is Nothing Then Exit Sub

‘Check for single-cell choice.

If selRange.Rows.Rely = 1 Then

    MsgBox “You have chosen just one cell. ” _

      & “Please choose a number of contiguous cells ” _

      & “inside a single column.”, vbOKOnly

    Exit Sub

Finish If

‘Set counter to 0.

j = 0

‘Cycle by means of user-selected vary to spotlight duplicates.

For Every curCell In selRange

    If WorksheetFunction.CountIf(selRange, curCell.Worth) > 1 Then

        curCell.Inside.ColorIndex = 36

        j = j + 1

    Finish If

Subsequent curCell

j = j / 2

MsgBox “You’ve got ” & j & ” duplicates.”, vbOKOnly

Finish Sub

Itemizing B

Sub DeleteDuplicates()

‘Spotlight duplicates in chosen vary.

Dim selRange As Vary

Dim curCell As Vary

‘Immediate person to pick out vary.

‘Will need to have Break on Unhandled Errors chosen.

On Error Resume Subsequent

Set selRange = Software.InputBox( _

    Title:=”Choose vary”, _

    Immediate:=”Choose a spread to test for duplicate values.”, _

    Sort:=8)

On Error GoTo 0

‘Check for cancel.

If selRange Is Nothing Then Exit Sub

‘Check for single-cell choice.

If selRange.Rows.Rely = 1 Then

    MsgBox “You have chosen just one cell. ” _

      & “Please choose a number of contiguous cells ” _

      & “inside a single column.”, vbOKOnly

    Exit Sub

Finish If

‘Cycle by means of user-selected vary to spotlight duplicates.

For Every curCell In selRange

    If WorksheetFunction.CountIf(selRange, curCell.Worth) > 1 Then

        curCell.Inside.ColorIndex = 0

    Finish If

Subsequent curCell

Finish Sub

If you happen to’re utilizing a ribbon model, remember to save the workbook as a macro-enabled file or the process will not run. If you happen to’re utilizing a menu model, you possibly can skip this step.

SEE: Home windows 10: Lists of vocal instructions for speech recognition and dictation (free PDF) (TechRepublic)

To enter each procedures, press Alt + F11 to open the Visible Fundamental Editor. Within the Mission Explorer to the left, choose ThisWorkbook so you possibly can run the process in any sheet. You possibly can enter the code manually or import the downloadable .cls file. As well as, the macro is within the downloadable .xlsm and .xls information. If you happen to enter the code manually, do not paste from this net web page. As an alternative, copy the code right into a textual content editor after which paste that code into the ThisWorkbook module. Doing so will take away any phantom net characters that may in any other case trigger errors.

Use the procedures

After getting into the procedures, you are able to put them to work as follows:

  1. Click on the Builders tab.
  2. Click on Macros within the Code group.
  3. Within the ensuing dialog, select HighlightDuplicates() as proven in Determine A and click on Run.
  4. When prompted, choose B3:B9 as proven in Determine B, and click on OK.
  5. Click on OK to shut the knowledge message that shows the variety of duplicates, proven in Determine C.

Determine A

excelvbaduplicates-a.jpg

  This process highlights duplicate values. 

Determine B

excelvbaduplicates-b.jpg

  Choose the vary you are checking for duplicates.

Determine C

excelvbaduplicates-c.jpg

The counting variable tracks the variety of duplicates.

That is it! As you possibly can see in Determine C, the process discovered two duplicates. These 4 cells are actually yellow, which implies two values have a replica: John and Doris. Discover that the code is not case delicate (Doris and doris).

The On Error statements catch keystrokes as an alternative of a spread choice. The For-loop cycles by means of every cell within the chosen vary and applies a spotlight when the expression

WorksheetFunction.CountIf(selRange, curCell.Worth) > 1

is TRUE, that means the present worth happens greater than as soon as within the knowledge set. The COUNTIF() operate is similar you’d use if making a conditional formatting rule. The very last thing the process does is show the variety of duplicate values, on this case, that is two.

Subsequent, run DeleteHighlights(), choose the identical vary and the process will shortly take away the highlights. Each procedures test for a Cancel click on and a single-cell vary.

Issues to think about

Each procedures are pretty versatile. They consider textual content, numbers, and dates, even when they’re in the identical knowledge vary. They each work with a number of columns, evaluating all of the entries as an entire. In different phrases, the code finds duplicates in the identical column in addition to duplicates shared throughout a number of columns. The code ignores blanks and is not case-sensitive.

You can make the method extra dynamic by grabbing the fill coloration earlier than making use of the highlighting in Itemizing A and saving it as a static variable that maintains its worth. Itemizing B might then reference that variable and use it to reset the cell’s fill coloration when eradicating highlighting. 

It is unlikely that you will wish to work by means of all these steps each time you wish to run the process. As an alternative, add the macro to the Fast Entry Toolbar. To take action, learn How one can add Workplace macros to the QAT toolbar for fast entry.  

Additionally see

Source link