I have tried for the better part of 2 days to find some code for counting unique values in a range and so far nothing has worked. I suspect it has more to do with the code I have to identify the range containing the values than the coding that I found.
The brief explanation as to why I have this code to find the ranges is that we have reports generated daily that were designed poorly up front but we (the end users) have no control for having them improved.
The end result of the following code is to identify the range for the count for each worksheet (this portion working). So rng4 will result in something like $E$34:$E$406.
What I am looking for is code that can reference "rng4" and count the unique values therein. The results of that count will then be placed in cell B1.
I would appreciate any help/advice that I can get on this.
Sub RenameTabsV2() Dim Wb As Workbook Dim ws As Worksheet Dim rng1 As Range Dim rng2 As Range Dim rng3 As Range Dim rng4 As Variant Dim rng5 As Range Dim LastRow As Long Set Wb = ActiveWorkbook 'Rename each sheet in workbook For Each ws In ActiveWorkbook.Worksheets Set rng1 = ws.Range("A:A").Find("Group Number") If Not rng1 Is Nothing Then ws.Name = rng1.Offset(1, 0).Value End If 'Identify if the data to count is in column E or C then identify the starting and stopping cells for the count On Error Resume Next If ws.CodeName = "Sheet1" Then Set rng2 = ws.Range("E:E").Find("Employee SSN", lookat:=xlPart) LastRow = ws.UsedRange.Rows.Count + ws.UsedRange.Rows(1).Row - 1 Set rng3 = ws.Range("E" & LastRow) rng4 = rng2.Offset(1, 0).Address & ":" & rng3.Address Else Set rng2 = ws.Range("C5") LastRow = ws.UsedRange.Rows.Count + ws.UsedRange.Rows(1).Row - 1 Set rng3 = ws.Range("C" & LastRow) rng4 = rng2.Address & ":" & rng3.Address End If ws.Range("A1") = rng4 'Temp value to use for testing/verifying the range on each sheet. To be replaced once the unique counts coding is functional. 'Range("A1") = "EE Count:" Range("B1") = Application.WorksheetFunction.SumProduct((rng4 <> "") / Application.WorksheetFunction.CountIf(rng4, rng4)) Next ws End Sub