Hello,
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.
Thanks
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
Display More