{"id":861,"date":"2016-08-01T23:37:34","date_gmt":"2016-08-02T03:37:34","guid":{"rendered":"http:\/\/regina-whipp.com\/blog\/?p=861"},"modified":"2018-07-18T22:37:18","modified_gmt":"2018-07-19T02:37:18","slug":"hide-duplicate-rows-in-excel","status":"publish","type":"post","link":"https:\/\/regina-whipp.com\/blog\/?p=861","title":{"rendered":"Hide Duplicate Rows in Excel…"},"content":{"rendered":"

Last year when I was really starting to jump into exporting data to Excel I ran into an issue. I wanted the rows with duplicated data to only show the first row and hide the following rows… Conditional Formatting, right? So, why couldn’t I get what I wanted. When all else fails *Google* it! Which is just what I did and found exactly what I needed on Contextures Blog<\/a> then\u00a0just convert the Excel Macro into VBA and this…<\/p>\n

     Const xlUp As Long = -4162 'This can go at the very tip under the Declarations\r\n     Dim ApXL As Object\r\n     Dim xlWBk As Object\r\n     Dim xlWSh As Object\r\n     Dim lngLastRow As Long\r\n     Dim Z As String\r\n\r\nWith xlWSh\r\n     'Conditional Formatting\r\n     lngLastRow = xlWSh.Cells(.Rows.Count, 1).End(xlUp).Row\r\n     Z = "A4" & ":F" & lngLastRow\r\n                \r\n     xlWSh.Range(Z).Select\r\n     '.Add Type:xlExpression use 1 for Early Binding, 2 for Late Binding\r\n     ApXL.Selection.FormatConditions.Add Type:=2, Formula1:="=A3=A2"\r\n     ApXL.Selection.FormatConditions(ApXL.Selection.FormatConditions.Count).SetFirstPriority\r\n          With ApXL.Selection.FormatConditions(1).Font\r\n               .Color = RGB(255, 255, 255)\r\n               .TintAndShade = 0\r\n          End With\r\n     ApXL.Selection.FormatConditions(1).StopIfTrue = False\r\nEnd With\r\n<\/pre>\n
…produces this…<\/p>\n
\"\"<\/span><\/p>\n
Note, the code includes some caveats… I never knew how many rows hence *Z* and *lngLastRow* and since I have a Totals row I needed to know for highlighting and summing. If you don’t have that limitation you can use (change the column and row numbers accordingly)…<\/p>\n
xlWSh.Range("D4:D100").Select<\/pre>\n
…and eliminate *Z* and *lngLastRow*.<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
Last year when I was really starting to jump into exporting data to Excel I ran into an issue. I wanted the rows with duplicated data to only show the first row and hide the following rows… Conditional Formatting, right? So, why couldn’t I get what I wanted. When all else fails *Google* it! Which […]<\/p>\n
<\/div>\n
<\/path><\/svg><\/i> \"Loading\"<\/p>\n
<\/div>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[5,79],"tags":[34,21],"class_list":["post-861","post","type-post","status-publish","format-standard","hentry","category-access-tips","category-excel","tag-excel","tag-vba","odd"],"_links":{"self":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/861","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=861"}],"version-history":[{"count":7,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/861\/revisions"}],"predecessor-version":[{"id":1344,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/861\/revisions\/1344"}],"wp:attachment":[{"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=861"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=861"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/regina-whipp.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=861"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}