numbers of unique entries in range, VBA

Question

I always receive type missmatch errors or division by zero errors while trying to implement following: I just want to count the number of unique entries in a range, the entries in the range are of "class" text:

startRow = 3
startColumn = 1
col = "A"
Set topCell = Cells(startRow, startColumn)
Set bottomCell = Cells(Rows.Count, startColumn)
If IsEmpty(bottomCell) Then Set bottomCell = bottomCell.End(xlUp)
Set selectRows = Range(col & topCell.Row & ":" & col & bottomCell.Row)
nRows = WorksheetFunction.CountA(selectRows)

test = WorksheetFunction.SumProduct(WorksheetFunction.IsText(selectRows) / WorksheetFunction.CountIf(selectRows, selectRows))

I have a bug in the computation for test, but I don't get it. Some help very appreciated

Thanks a lot

BR Martin

Although `IsText` will take a `Range` object in a worksheet function, I do not think it will accept a `Range` in VBA? I've been playing around with it and have not been able to get the same behavior of a the worksheet function within VBA — psubsee2003, May 11 '12 at 13:03

score 0 · Accepted Answer · edited May 23 '17 at 10:09

Your first problem is the WorksheetFunction.CountIf(selectRows, selectRows) part of your test calculation. When there are no duplicates, this will result in a division by zero error. This will occur when typed into a worksheet as well, so you will either need to change your logic, or test for this case first.

Your Type Mismatch problem, I believe, is caused by the WorksheetFunction.IsText(selectRows) segment. I have not been able to figure out what is causing it, but as i mentioned in my comments, I think the IsText() function may not take a range in VBA like it does when typed into a cell.

I would probably approach this problem in a different way. Here's an example I found elsewhere on SO Count unique values in Excel This mostly has worksheet formulas, but there is 1 answer with VBA code that you probably could adapt.

Another option is to create a collection and count the number of elements

Sub CountUnique()
Dim Col As New Collection
Dim i As Integer

On Error Resume Next

For i = 3 To 10
    Col.Add Sheet1.Cells(i, 1).Value, Sheet1.Cells(i, 1).Value
Next

MsgBox Col.Count

On Error GoTo 0
End Sub

numbers of unique entries in range, VBA

1 Answers1