Hide Duplicate Rows in Excel…

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 then just convert the Excel Macro into VBA and this…

     Const xlUp As Long = -4162 'This can go at the very tip under the Declarations
     Dim ApXL As Object
     Dim xlWBk As Object
     Dim xlWSh As Object
     Dim lngLastRow As Long
     Dim Z As String

With xlWSh
     'Conditional Formatting
     lngLastRow = xlWSh.Cells(.Rows.Count, 1).End(xlUp).Row
     Z = "A4" & ":F" & lngLastRow
                
     xlWSh.Range(Z).Select
     '.Add Type:xlExpression use 1 for Early Binding, 2 for Late Binding
     ApXL.Selection.FormatConditions.Add Type:=2, Formula1:="=A3=A2"
     ApXL.Selection.FormatConditions(ApXL.Selection.FormatConditions.Count).SetFirstPriority
          With ApXL.Selection.FormatConditions(1).Font
               .Color = RGB(255, 255, 255)
               .TintAndShade = 0
          End With
     ApXL.Selection.FormatConditions(1).StopIfTrue = False
End With

…produces this…

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)…

xlWSh.Range("D4:D100").Select

…and eliminate *Z* and *lngLastRow*.

 996 total views,  1 views today