Sizing dynamic arrays based on data within a worksheet

[MarkVMcCullagh]

I have a worksheet that contains date/time data in Columns A-D
I have managed to load these into a VB static array. Once some calculations have been done the answers are posted back to elsewhere on the worksheet using a second array.

What I would like to do is generate a dynamic array that matches the number of rows of data involved. I am speaking about the first array ie. firstrange. The reason I want to convert this to dynamic rather than static is that it is more efficient and hopefully it won't crash when it encounters a row without data.

For your info columns A and C contain dates and columns B and D contain dates. Basically what I am doing is (C+D)-(A+B) for each row.

This is my first attempt at VB so I know my code will look rough.


Sub CategoriseCalls()
Dim firstrange As Variant
Dim secondrange As Variant
Dim i As Long
Dim VarMY As Date
firstrange = Range("a2:d65532").Value ' Select all cells in Column a-d
ReDim secondrange(1 To UBound(firstrange, 1), 1 To 2)
Var4 = 4 / 24 '4 hours
Var6 = 6 / 24 '6 hours
Var8 = 8 / 24 '8 hours
For i = 1 To UBound(firstrange, 1)
If (firstrange(i, 4) + firstrange(i, 3)) - (firstrange(i, 2) + firstrange(i, 1)) <= Var4 Then
secondrange(i, 1) = 4
Else
If (firstrange(i, 4) + firstrange(i, 3)) - (firstrange(i, 2) + firstrange(i, 1)) <= Var6 Then
secondrange(i, 1) = 6
Else
If (firstrange(i, 4) + firstrange(i, 3)) - (firstrange(i, 2) + firstrange(i, 1)) <= Var8 Then
secondrange(i, 1) = 8
Else
secondrange(i, 1) = 9
End If
End If
End If
VarMY = DateValue(Month(firstrange(i, 1)) & "/" & Year(firstrange(i, 1)))
secondrange(i, 2) = VarMY
Next i
Range("j2").Resize(UBound(firstrange, 1), 2).Value = secondrange
MsgBox "Completed"
End Sub

 

[TommyGun]

Just check the length of your list before assigning it to an array. Test this to see if it works as needed.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> CategoriseCalls()
    <SPAN style="color:#00007F">Dim</SPAN> firstrange()
    <SPAN style="color:#00007F">Dim</SPAN> secondrange()
    
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, lastRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
        
    <SPAN style="color:#00007F">Dim</SPAN> VarMY <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Date</SPAN>

    lastRow = Range("A65536").End(xlUp).Row
    
    firstrange = Range("a2:d" & lastRow).Value <SPAN style="color:#007F00">' Select all cells in Column a-d</SPAN>
    
    <SPAN style="color:#00007F">ReDim</SPAN> secondrange(1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(firstrange, 1), 1 <SPAN style="color:#00007F">To</SPAN> 2)
    
    var4 = 4 / 24 <SPAN style="color:#007F00">'4 hours</SPAN>
    var6 = 6 / 24 <SPAN style="color:#007F00">'6 hours</SPAN>
    var8 = 8 / 24 <SPAN style="color:#007F00">'8 hours</SPAN>

    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(firstrange, 1)
        <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> (firstrange(i, 4) + firstrange(i, 3)) - (firstrange(i, 2) + firstrange(i, 1))
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= var4: secondrange(i, 1) = 4
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= var6: secondrange(i, 1) = 6
            <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Is</SPAN> <= var8: secondrange(i, 1) = 8
            <SPAN style="color:#00007F">Case</SPAN> Else: secondrange(i, 1) = 9
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
    
        VarMY = DateValue(Month(firstrange(i, 1)) & "/" & Year(firstrange(i, 1)))
        
        secondrange(i, 2) = VarMY
    <SPAN style="color:#00007F">Next</SPAN> i

    Range("j2").Resize(UBound(firstrange, 1), 2).Value = secondrange

    MsgBox "Completed"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[SIXTH SENSE]

hi!
try this one!

dim firstrange()
dim tmp as variant
set  tmp= Range("a2:d" & range("a65536").end(xlup).row)
firstrange=tmp.value

This would create an array of size equal to the size of the cell having content.

 

[MarkVMcCullagh]

Thanks TommyGun as your solution works. Also thanks to SixthSense for your suggestion.