{"id":1028,"date":"2014-07-30T09:03:47","date_gmt":"2014-07-30T08:03:47","guid":{"rendered":"http:\/\/oprsteny.cz\/?p=1028"},"modified":"2014-07-30T09:03:47","modified_gmt":"2014-07-30T08:03:47","slug":"excel-ignore-blanks-and-duplicates-in-validation-list","status":"publish","type":"post","link":"https:\/\/oprsteny.cz\/?p=1028","title":{"rendered":"Excel &#8211; Ignore blanks and duplicates in Validation list"},"content":{"rendered":"<p>I faced a problem where I had a worksheet where in column A there were items I wanted to use as source for validation list in another column. But these items were not unique, therefore they appeared in the validation list not just once. So I used the solution presented below to automatically create a unique list in a spare column and assign that list as the source of data validation drop down list for a specified cell range<!--more--><\/p>\n<ul>\n<li>Column A &#8211; Source column with duplicates<\/li>\n<li>Column B &#8211; Filtered column computed by the macro (used as source list for validation)<\/li>\n<li>Column C &#8211; Validated column against values in column <em>B<\/em><\/li>\n<\/ul>\n<pre lang=\"vbasic\">Private Sub Worksheet_Change(ByVal Target As Range) \r\n  Dim i, LastRow, LastRow_B\r\n\r\n  Application.ScreenUpdating = False   \r\n  Application.EnableEvents = False \r\n  Columns(\"B:B\").Hidden = True \r\n  Columns(\"B:B\").ClearContents \r\n  LastRow = Application.Cells. SpecialCells(xlCellTypeLastCell).Row \r\n\r\n  For i = 1 To LastRow \r\n    If Application.CountIf(Range(\"B:B\"), Cells(i, \"A\").Value) = 0 Then \r\n      Range(\"B\" &amp; Rows.Count).End(xlUp).Offset(1).Value = Cells(i, \"A\").Value \r\n    End If \r\n  Next \r\n\r\n  LastRow_B = Range(\"B\" &amp; Rows.Count).End(xlUp).Row \r\n\r\n  Range(\"C:C\").Validation.Delete \r\n  With Range(\"C:C\").Validation \r\n    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _ \r\n    Operator:=xlBetween, Formula1:=\"=$B$2:$B$\" &amp; LastRow_B\r\n    .IgnoreBlank = True \r\n    .InCellDropdown = True \r\n    .InputTitle = \"\" \r\n    .ErrorTitle = \"\" \r\n    .InputMessage = \"\" \r\n    .ErrorMessage = \"\" \r\n    .ShowInput = True \r\n    .ShowError = True \r\n  End With \r\n\r\n  Application.EnableEvents = True \r\nEnd Sub\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I faced a problem where I had a worksheet where in column A there were items I wanted to use as source for validation list in another column. But these items were not unique, therefore they appeared in the validation &hellip; <a href=\"https:\/\/oprsteny.cz\/?p=1028\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"Excel - Ignore blanks and duplicates in Validation list","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2},"jetpack_post_was_ever_published":false},"categories":[9,38,5],"tags":[305,304,306,308,267,307],"class_list":["post-1028","post","type-post","status-publish","format-standard","hentry","category-development","category-software","category-tools","tag-blanks","tag-excel","tag-ignore","tag-macro","tag-validation","tag-vbasic"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3nYbe-gA","jetpack-related-posts":[],"_links":{"self":[{"href":"https:\/\/oprsteny.cz\/index.php?rest_route=\/wp\/v2\/posts\/1028","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oprsteny.cz\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oprsteny.cz\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oprsteny.cz\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oprsteny.cz\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1028"}],"version-history":[{"count":1,"href":"https:\/\/oprsteny.cz\/index.php?rest_route=\/wp\/v2\/posts\/1028\/revisions"}],"predecessor-version":[{"id":1029,"href":"https:\/\/oprsteny.cz\/index.php?rest_route=\/wp\/v2\/posts\/1028\/revisions\/1029"}],"wp:attachment":[{"href":"https:\/\/oprsteny.cz\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1028"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oprsteny.cz\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1028"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oprsteny.cz\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1028"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}