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.
Excel can fly with VBA!
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 |
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!