In the VBA formula, need to get starting row number from the cell value

[motilulla]

Hello,</SPAN></SPAN>

In the cell H6 I have a formula =COUNTIF(D$6:D$100,G6) which start counting unique data of column D from the starting row 6, I need help how can I change starting row number 6 to x row "in the formula above which I am running via VBA" does it is possible VBA formula can pick starting row number from the cell E1 and become =COUNTIF(D$15:D$100,G6)</SPAN></SPAN>

Sub CountUniques()
    Range("H6:H15").Formula = "=COUNTIF(D$6:D$100,G6)"
    Range("H7:H15") = Range("H7:H15").Value
End Sub

Book1
	D	E	F	G	H
1		15
2
3
4
5	Data			Uniques Numbers	Counts
6	1			1	6
7	1			2	0
8	1			3	0
9	4			4	4
10	5			5	4
11	6			6	4
12	7			7	2
13	9			8	1
14	10			9	2
15	10			10	3
16	10
17	9
18	8
19	7
20	6
21	6
22	6
23	5
24	5
25	5
26	4
27	4
28	4
29	1
30	1
31	1
Sheet1

Thank you all </SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN>
Moti</SPAN></SPAN>

 

[Fluff]

You can use this formula
=COUNTIF(INDEX(D$1:D$100,E$1):D$100,G6)

 

[Fluff]

Or with VBA

Sub CountUniques()
    With Range("H6:H15")
        .Value = Evaluate("if({1},COUNTIF(INDEX(D$1:D$100,E$1):D$100," & .Offset(, -1).Address & "))")
    End With
End Sub

 

[motilulla]

You can use this formula
=COUNTIF(INDEX(D$1:D$100,E$1):D$100,G6)

Or with VBA

Sub CountUniques()
    With Range("H6:H15")
        .Value = Evaluate("if({1},COUNTIF(INDEX(D$1:D$100,E$1):D$100," & .Offset(, -1).Address & "))")
    End With
End Sub

Fluff, the VBA formula is spot on, and so kind of you for the additional formula </SPAN></SPAN>

Very much appreciated your prompt reply and kind help</SPAN></SPAN>

Kind Regards</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[Fluff]

Glad to help & thanks for the feedback