Search code examples
vbaexceldynamicdistinct-values

Counting unique text values and making dynamic dashboard in VBA

enter image description here

I have a reference file for a dashboard I am making. I want to be able to count the unique org names in a reference file column and make that number the amount of column headers in my separate dashboard. I used macro recorder to make it unique, but I am unsure of how to translate this into making a dynamic number of columns for my dashboard based on the count of unique org names in the ref file. Here's a picture attachment example of what the reference file could look like. So, if it counts that there are 5 unique names, I would like the separate dashboard to make 5 columns with those names as headers in each column.

  Sub Macro1()


 '     Macro1 Macro

Columns("F:F").Select
Range("F1:F10000000000").AdvancedFilter Action:=xlFilterCopy,  CopyToRange:=Columns _
    ("O:O"), Unique:=True

ActiveCell.FormulaR1C1 = "=ROWS(R[-11]C:R[-2]C)"

 End Sub
Solution

  • Here's a way to do it by loading the unique results into an array. This assumes the column headers go into A1.

    Sub Macro1()

Dim wsRef As Worksheet
Dim wsDB As Worksheet

Set wsRef = Worksheets("reference")
Set wsDB = Worksheets("Dashboard")

With wsRef
    .Range("C1:C9").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=.Range("F1"), Unique:=True

    Dim arrValues As Variant
    arrValues = .Range("F2", .Range("F" & .Rows.Count).End(xlUp))

End With

With wsDB
    .Range(.Range("A1"), .Cells(1, UBound(arrValues))).Value = Application.WorksheetFunction.Transpose(arrValues)
End With

End Sub