VBA refer to a column used range in a formula

Question

I have an array formula used to count unique records in a column. I am trying to use it in a VBA module, but need to figure out how to have the range change based on the used range of the column as it will change each time I run it. The formula I'm using is =SUM(1/COUNTIF(H2:H67,H2:H67)), just need to understand how to change the ranges in column H dynamically. Any help is appreciated.

I have researched online but am coming up short on how to isolate the used range to a single column and how to reference that in the formula I have.

Answer 1

Here's an example of using VBA to evaluate a worksheet formula:

Sub Tester()
    
    Dim ws As Worksheet, lr As Long
    
    Set ws = ActiveSheet 'for example
    
    lr = ws.Cells(Rows.Count, "H").End(xlUp).Row 'last row in col H
    
    If lr > 1 Then 'any data?
        'evaluate the formula
        Debug.Print ws.Evaluate(Replace("=SUM(1/COUNTIF(H2:H<lr>,H2:H<lr>))", "<lr>", lr))
        
        'alternate using UNIQUE()
        Debug.Print UBound(ws.Evaluate("=UNIQUE(H2:H" & lr & ")"))
    End If
    
End Sub