Dynamic range with formula-based end

[Patrunjel]

Hi,
I would really appreciate if you could help me with something that I think it is not hard for someone that knows vba well. I don't.
I am trying to select a range whose end is variable. The end of the range is dependent on a formula that tries to find unique values. The fomula is =SUMPRODUCT(1/countif(rng, rng)I am a newbie to VBA and I don't know how to insert the end of the range to display the result of the SUMPRODUCT formula.

My code is:

<!-- BEGIN TEMPLATE: bbcode_code -->
 
Sub dstc()     
Sheets("Sheet1").SelectSheets("Sheet1").Range("H4:H" & Sheets("Sheet1").Range("H1").Value).Select
Selection.FormulaArray = "=distinctvalues(rng_A,TRUE)" 
End Sub

So far I've tricked it by pointing to a cell (H1) that displays the result of the sumproduct, but somebody may delete it someday. My dream is to have something such as:

<!-- BEGIN TEMPLATE: bbcode_code -->
Sheets("Sheet1").Range("H4:H" & [B]formula[/B] ).Value).Select

Thank you in advance for any help on this!
​