VBA – Array to Excel Range. With filtered rows.

Excel with VBA is really a powerful tool. There should be some 5 more years, until Python really becomes a standard for the Excel people and I am not sure that it would actually happen that quickly.

Anyway, working with Ranges and Arrays in Excel and VBA saves time. In this article, I will show how to write a VBA Array to Excel range. Even when the rows are filtered.

When the rows are filtered, and we still have to write in the filtered rows, a simple loop from the array into the range actually works like a charm:

In the video below, using the code that is also below, the column G of the picture above is filled out (that is a sentence, definitely not generated by Chat GPT :)).

Anyway, here is the code:

Sub ExcelArrayToRange()

    Dim mySelection As Range
    Const START_ROW = 3
    
    With Sheet1
        Set mySelection = .Range( _
                        .Cells(START_ROW, "B"), _
                        .Cells(LastRow(.Cells.Parent, 1), LastColumn(.Cells.Parent, 1)))
    End With
    
    Dim myArray As Variant
    myArray = mySelection
    
    PrintMultidimensionalArrayExample myArray

    Dim numbers As Variant
    numbers = Array(3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17)
    
    Dim i As Long
    For i = START_ROW To mySelection.Rows.Count + START_ROW - 1
        If UBound(numbers) >= i Then
            Sheet1.Cells(i, "G") = numbers(i)
        End If
    Next i
        
End Sub

And in order to make sure it works like a charm, use the functions below as well:

Sub PrintMultidimensionalArrayExample(myArray)
    
    Dim myRange As Range
    Set myRange = Range("B3:F17")
    
    myArray = myRange
    
    'Debug.Print UBound(myArray, 1)  'count of excel cells in a column
    'Debug.Print UBound(myArray, 2)  'count of excel cells in a row
    
    'Debug.Print LBound(myArray, 1)  'index of first cell in column
    'Debug.Print LBound(myArray, 2)  'index of first cell in row
    
    Dim i As Long
    For i = 1 To UBound(myArray, 1)
        PrintArray GetRowFromMdArray(myArray, i)
    Next
        
    'PrintArray GetRowFromMdArray(myArray, 1)
    'PrintArray GetColumnFromMdArray(myArray, UBound(myArray, 2))

End Sub

Public Sub PrintArray(myArray As Variant)

    Dim i As Long
    For i = LBound(myArray) To UBound(myArray)
        Debug.Print i & " --> " & myArray(i)
    Next i
    
End Sub

Function GetColumnFromMdArray(myArray As Variant, myCol As Long) As Variant
    
    'returning a column from multidimensional array
    'the returned array is 0-based, but the 0th element is Empty.
    
    Dim i As Long
    Dim result As Variant
    Dim size As Long: size = UBound(myArray, 1)
    ReDim result(size)
    
    For i = LBound(myArray, 1) To UBound(myArray, 1)
        result(i) = myArray(i, myCol)
    Next
    
    GetColumnFromMdArray = result
    
End Function

Function GetRowFromMdArray(myArray As Variant, myRow As Long) As Variant
    
    'returning a row from multidimensional array
    'the returned array is 0-based, but the 0th element is Empty.
    
    Dim i As Long
    Dim result As Variant
    Dim size As Long: size = UBound(myArray, 2)
    ReDim result(size)
    
    For i = LBound(myArray, 2) To UBound(myArray, 2)
        result(i) = myArray(myRow, i)
    Next
    
    GetRowFromMdArray = result
    
End Function

Public Function LastColumn(ws As Worksheet, Optional rowToCheck As Long = 1) As Long

    LastColumn = ws.Cells(rowToCheck, ws.Columns.Count).End(xlToLeft).Column
    
End Function

Public Function LastRow(ws As Worksheet, Optional columnToCheck As Long = 1) As Long
    
    LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

Enjoy!