Make an addressable Variant Array of some data from a SHEET

[Mackeral]

Here's the code:

Function Make_Variant_Array(SHEET As Worksheet, ParamArray Col_Nrs()) As Variant
    ' Return an Array from a SHEET for each row containing the data from particular "Columns".
    ' 8/29/20 Created. Mac Lingo
   
    ' Note: Since you can only resize the last dimension of the ReDim statement, the Rows and Columns  get reversed.
   
    Dim Args() As Variant
    Sheet_Name = SHEET.Name
    Dim Columns(1 To 10) As Integer
   
    Col_Knt = 0
    For Each Value In Col_Nrs
        Col_Knt = Col_Knt + 1
        Columns(Col_Knt) = Value
    Next
   
    ReDim Preserve Args(Col_Knt, 0)
    Args(0, 0) = 1: Args(1, 0) = 1: Args(2, 0) = 2
   
    LastRow = Last_Row(SHEET)
    For Row = 1 To LastRow
        ReDim Preserve Args(Col_Knt, Row)
       
        For Col = 1 To Col_Knt
            Args(Col, Row) = SHEET.Cells(Row, Columns(Col))
        Next
       
        Row_Knt = Row_Knt + 1
    Next Row
   
    Args(1, 0) = LastRow
    Make_Variant_Array = Args
End Function ' Make_Variant_Array

The the tricky thing about accsssing the data is the Row and Col data are interchanged, so to get the data from a Row/Column, you have to access it like this:

TS = Variant_Data(Column, Row)

The reason it had to be coded this way is because the second argument in the ReDim statement is the only one you can change on the fly. And it took a bit of scrounging about to find that this was the way it worked.

I developed this because I needed to have data data from a file sorted in a different way that the file was ordinarily organized. So I sorted by what I was interested in, made an array of what I wanted in that order, and then resorted the file back into it's most useful order.

 

[mikerickson]

One workaroud to Redim the first index of a 2-dimensional array

Dim TwoDArray as Variant
Dim WorkArray as Variant

TwoDArray = Range("A1:D100").Value: ' some values

WorkArray = Application.Transpose(TwoDArray)

ReDim Preserve WorkArray(1 to UBound(WorkArray,1), 1 to 150)

TwoDArray = Application.Transpose(WorkArray)

 

[jasonb75]

What am I missing? Is there a question hidden in there somewhere or are you just posting it for opinion / review?

You're calling another function Last_Row() for which you haven't provided the code. While it appears obvious what that function does, the function that you have posted is unlikely to work without it, or to work correctly if we write our code for it and make incorrect assumptions for that which appears obvious.

 

[Mackeral]

Just posting this for other people to use
.
Is Excel 2019 the same as Office 365?

Is there a way to mark a post as a Question or something that might be useful to look at. I like searching to be general and I've often found an answer to a problem I'm having in replys to somebody else's posts.

 

[jasonb75]

Is Excel 2019 the same as Office 365?

365 is a subscription that is regularly updated with the latest new features, comparing it to Excel 2016, 2019 etc. In layman's terms (not exact science of proven fact), 365 is equivalent to the next version of excel due to be released, e.g. 365 at the moment is roughly the same as Excel 2022 will be.

I've not seen a way to mark a post in a specific way other than to mention that you are only posting something that others might find useful.

As far as I know, anyone is allowed to post in 'Excel Articles' (a different area of the forum) but I'm not sure what the requirements (if any) are for acceptable content there.