Learning arrays ... error when trying to read from range using cells property

[franklin_m]

I've got a spreadsheet full of varying types of data, numbers, blanks, text, etc. It's 37 rows and 159 columns. I'm trying to learn to use arrays, and I wrote the appended code to read the data into the array. I realize they way I do somethings, like get number of rows and columns can be done better, but I'm trying to write code that's at the same time fast and easier for folks to follow if they don't have as much knowledge and need to look through it. Thanks.

When I run this code, I get run-time error '1004': Method "range' of object '_Worksheet' failed. Debug shows the line beginning with "ArrayValues = ws_source.Range....."

Two asks. How to fix and why did what I wrote fail? Trying to learn. Thanks.

Sub ArrayTest_v1()

    Dim ws_source As Worksheet
    Set ws_source = Worksheets("Matrix Data")

    Dim RowsInSheet As Long
    RowsInSheet = 0
    RowsInSheet = WSFindRows(ws_source, 1, 1)

    Dim ColumnsInSheet As Long
    ColumnsInSheet = 0
    ColumnsInSheet = WSFindColumns(ws_source, 1, 1)

Dim ArrayValues() As Variant
ArrayValues = ws_source.Range(Cells(1, 1), Cells(RowsInSheet, ColumnsInSheet)).Value

End Sub

Private Function WSFindRows(WSName As Worksheet, BeginRow As Long, BeginCol As Long) As Long
    Dim yy As Long
    yy = BeginRow
    Do While WSName.Cells(yy, BeginCol) <> ""
        yy = yy + 1
    Loop
    WSFindRows = yy - 1
End Function

Private Function WSFindColumns(WSName As Worksheet, BeginRow As Long, BeginCol As Long) As Long
    Dim xx As Long
    xx = BeginCol
    Do While WSName.Cells(BeginRow, xx) <> ""
        xx = xx + 1
    Loop
    WSFindColumns = xx - 1
End Function

 

[Cubist]

I think the issue lies with your WSFindColumns/Rows. Suppose your data includes technically empty cells but with whitespace or formatting. In that case, the loop will continue beyond the actual data, resulting in an incorrect count of rows or columns.

There are much more robust and simpler ways to find the last row and column. See here.

 

[Fluff]

The problem is you haven't specified the sheet for all the ranges, try

ArrayValues = ws_source.Range(ws_source.Cells(1, 1), ws_source.Cells(RowsInSheet, ColumnsInSheet)).Value

 

[franklin_m]

The problem is you haven't specified the sheet for all the ranges, try

ArrayValues = ws_source.Range(ws_source.Cells(1, 1), ws_source.Cells(RowsInSheet, ColumnsInSheet)).Value

That worked! Thank you so much. Now I'll have to do some reading to better understand when I need to specify sheet for ranges and when not...

 

[Fluff]

Glad we could help & thanks for the feedback.