When I code in VBA, it is very rare that I will deal with a fixed number of the objects. I like to use dynamic arrays and then use ReDim to resize the array to the necessary dimensions, which makes core more flexible and adoptable in the different place.
Dim PmtStream() As Variant
'----we read the date from Excel sheet to PmtStream. The count variable is used to count the number of values read in.
TotalCount = WorksheetFunction.CountIf(Range("F2:F100"), ">0")
'-----ReDim PmtStream to the size which is needed.
ReDim PmtStream(1 To TotalCount)
Count = 1
For i = 2 To 100
If Cells(6, i).Value > 0 Then
PmtStream(Count) = Cells(6, i).Value
Count = Count + 1
This is the good way to control the size of the array.