Can't suppress pop up when trying to remove duplicates, even with displayalerts=false

iPenguin

New Member
Joined
Sep 11, 2017
Messages
4
Column C in my spreadsheet contains values that will be client-chosen and frequently updated. I want column D to have data validation applied dynamically that pulls from that list. However, it needs to contain alphabetically ordered, unique values.


What I am currently doing is using the following formula to alphabetically order those values in a hidden column (BK). (Note: the site I found this on indicated it should only show unique values, however it did not).


{=INDEX(List,MATCH(0,IF(MAX(NOT(COUNTIF($BK$15:BK15,List))*(COUNTIF(List,">"&List)+1))=(COUNTIF(List,">"&List)+1),0,1),0))}


To update column D dynamically, I am using the following code:
Code:
Dim NewRng As Range
Dim RefList As Range, c As Range, rngHeaders As Range, RefList2 As Range, msg


On Error GoTo ErrHandling




Set NewRng = Application.Intersect(Me.Range("D16:D601"), Target)
If Not NewRng Is Nothing Then


    Set rngHeaders = Range("A15:ZZ16").Find("Status List", After:=Range("E15"))
    Set RefList = Range(rngHeaders.Offset(1, 0).Address, rngHeaders.Offset(100, 0).Address)
    RefList.Copy
    RefList.Offset(0, 1).PasteSpecial xlPasteValues
    Set RefList2 = RefList.Offset(0, 1)




    Application.DisplayAlerts = False
    RefList2.RemoveDuplicates Columns:=Array(1), Header:=xlNo




    For Each c In NewRng
        c.Validation.Delete
        c.Validation.Add Type:=xlValidateList, _
                                 AlertStyle:=xlValidAlertStop, _
                                 Formula1:="=" & RefList2.Address


    Next c
End If
Application.DisplayAlerts = True
Application.EnableEvents = True


This seems to work, except every time I click in a cell in column D it still throws a pop up box called "Remove Duplicates" that shows two checked checkboxes -- "Select All" and "Column BL". It also tells me how many duplicates were found and how many unique values will remain.


I am at a loss for why displayalerts=false hasn't turned this off, but it definitely isn't an option to have this fire every time someone clicks in column D. Has anyone seen this before? (I am on Excel for Mac 2016 by the way).

I am of course open to simpler ways of doing this if I am making this more complicated than it needs to be.
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Forum statistics

Threads
1,223,953
Messages
6,175,598
Members
452,658
Latest member
GStorm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top