Convert a Range to Array

Question

I have a Problem with my code.I want to get all Cells and to put These in an Array but it didnt work. Runtime Error 13. Range -> Array

For z = 0 To 3
Worksheets(Tabellen(z)).Select
AnzahlZellen = Application.WorksheetFunction.CountA(Range("A:A"))
    For n = 1 To AnzahlZellen

        'Worksheets(Tabellen(z)).Select'
        Range("A" & n).Select
        InhaltsArray(n) = Range("A" & n).Value

Storax · Answer 1 · 2018-10-12T06:09:46.893

In this case you could build a one-dimensioal array like that

Dim vDat as variant
Dim rg as range
Set rg = Range("A1:A" & AnzahlZellen)
vDat = WorksheetFunction.Transpose((rg))

In this way you can easily read from a range of cells to an array. You can also write from an array to a range of cells. Usually you get a 2D-array but as you only have one column you can convert it by Transpose into a 1D-array.

As you did not show the declaration of InhaltsArray I guess the run time error 13 is caused by some content of the range which does not fit the data type of InhaltsArray

As an additional comment to your code: Most of the time you do not need select

score 0 · Answer 2 · answered Oct 12 '18 at 06:13

It looks like you are trying to store 4 worksheets' column A values into a single 1-D array,

dim i as long, n as long, z as long, tmp as variant

for z=lbound(Tabellen) to ubound(Tabellen)

    with Worksheets(Tabellen(z))
        if z=lbound(Tabellen) then
            InhaltsArray = application.transpose(.range(.cells(1, "A"), .cells(.rows.count, "A").end(xlup)).value2
        else
            tmp = .range(.cells(1, "A"), .cells(.rows.count, "A").end(xlup)).value2
            i = ubound(InhaltsArray)
            redim preserve InhaltsArray(lbound(InhaltsArray) to (i + ubound(tmp, 1)))
            for n = lbound(tmp, 1) to ubound(tmp, 1)
                InhaltsArray(i + n) = tmp(n, 1)
            next n
        end if

    end with

next z

DSlomer64 · Answer 3 · 2021-06-23T12:07:40.720

The Answer submitted by @Storax is the basis for the following code, which defines function rangeToArray that will take a Range object and return an equivalent array:

Function rangeToArray(rg As Range)
  If rg.Count = 1 Then
    rangeToArray = Array(rg(1))
  Else
    rangeToArray = WorksheetFunction.Transpose(rg)
  End If
End Function

It can be used like so:

Sub test()
  Dim lastRowInA As Long, i As Integer

  Dim a()

  lastRowInA = WorksheetFunction.CountA(Range("A:A"))
  
  a = rangeToArray(Range("A1:A" & lastRowInA))

  For i = 1 To lastRowInA
    Debug.Print a(i)
  Next

End Sub

The if inside rangeToArray is necessary only if rg could consist of a single cell.

Also, if A has no populated rows, an error will result when using lastRowInA, which will be 0, to define the range being passed torangeToArray.

The single line from Storax, shown below and adapted in the Else in function rangeToArray, is a brilliant, simple key to solution.

vDat = WorksheetFunction.Transpose((rg))

Convert a Range to Array

3 Answers3