Quick array question

[MrTeeny]

Can anyone give e the syntax of how I'd create a variant array without setting the dimensions beforehand or using a range on a sheet

At the moment I basically

[B]Dim unclosed_liabilities_array As Variant[/B]

then set the array by finding the last row and selecting an empty space on the sheet

[B]unclosed_liabilities_array = sht_runners.Range("BB14:BA" & LrowRunners).Value[/B]

I'm guessing I need to use ReDim somewhere along the line but just need the syntax for setting the empty arrays dimensions which I assume are simply number of element and number of columns/

Thanks

 

[JackDanIce]

See if this helps:

Sub M1()

    Dim LR       As Long
    Dim LC       As Long
    Dim arr()   As Variant

    Application.ScreenUpdating = False
    
    With Sheets("Sheet1")
         LR = .Cells(.Rows.count, 1).End(xlUp).row
         LC = .Cells(1, .Columns.count).End(xlToLeft).column
        arr = .Cells(1, 1).Resize(LR, LC).Value
    End With
    
    Application.ScreenUpdating = True
    
End Sub

 

[MrTeeny]

Thanks I was hoping it'd be something simpler like below as I've seen ReDim in lots of coding here and there with regard to arrays. I basically now how many columns I'd want and the only variable would be how many records the array held.

ReDim unclosed_liabilities_array(2,LrowRunners)

 

[Norie]

You could create an undimensioned variant simply by re lading it.

Dim unclosed_liabilities_array() As Variant

 

[JackDanIce]

If you know the column count (e.g. 10), then you could adapt to:

Sub M1()

    Dim LR       As Long
    Dim arr()   As Variant

    Application.ScreenUpdating = False
    
    With Sheets("Sheet1")
         LR = .Cells(.Rows.count, 1).End(xlUp).row
        arr = .Cells(1, 1).Resize(LR, 10).Value
    End With
    
    Application.ScreenUpdating = True
    
End Sub

If you're reading a fixed number of columns with varying number of rows, I'm not sure why you'd to "pre" ReDim your array size, as you're still going to write the data to the array, like your example:

unclosed_liabilities_array = sht_runners.Range("BB14:BA" & LrowRunners).Value

Also, should that be "BB14:BA" & LrowRunners or "BA14:BB" & LrowRunners, given column BA is before BB?

 

[MrTeeny]

If you know the column count (e.g. 10), then you could adapt to:

Sub M1()

    Dim LR       As Long
    Dim arr()   As Variant

    Application.ScreenUpdating = False
    
    With Sheets("Sheet1")
         LR = .Cells(.Rows.count, 1).End(xlUp).row
        arr = .Cells(1, 1).Resize(LR, 10).Value
    End With
    
    Application.ScreenUpdating = True
    
End Sub

If you're reading a fixed number of columns with varying number of rows, I'm not sure why you'd to "pre" ReDim your array size, as you're still going to write the data to the array, like your example:

Also, should that be "BB14:BA" & LrowRunners or "BA14:BB" & LrowRunners, given column BA is before BB?

The BB:BA was a typo as I amended it on the fly for the purposes of a two column array

Thanks for your help , I thought there may be a simple way to get an empty array with set dimensions as it gets populated later but needs to be a set numbers of elements generated LrowRunners

 

[MrTeeny]

You could create an undimensioned variant simply by re lading it.

Dim unclosed_liabilities_array() As Variant

Thanks I had used that before but I'd want to set the array elements using LrowRunners to match another array size used to populate it rather than adding to the empty array as I went along, maybe the method I use at the moment is the simplest for now.

 

[Norie]

I'm confused, if you want an empty array with set dimensions just declare one.

Dim unclosed_liabilities_array(1 To 10, 1 To 20) As Variant

 

[JackDanIce]

There may be confusion with what you're trying to do and use of dimensionalised arrays.

ReDim with PRESERVE tends to be used when you're creating a array with respect to another array or data process for output where the size is unknown (e.g. filtering values from an input array to an output array).

I've not seen many situations where you need to initially set the size of the array, even if copying from an input array to output array, but that's not saying it doesn't exist either.

 

[MrTeeny]

I'm confused, if you want an empty array with set dimensions just declare one.

Dim unclosed_liabilities_array(1 To 10, 1 To 20) As Variant

I need an empty array but the dimensions aren't set , the number of elements will be set using the value in LrowRunners, I was looking for the syntax to set an array with LrowRunners elements in it I had tried

Dim unclosed_liabilities_array(1 To LrowRunners) As Variant

but it gives constant errors