Копирование диапазона в массив excel vba

I have a repeating list of 145 categories, with 15 columns of data for each category.
I am consolidating this list by reducing the number of categories to 24 and adding the corresponding data.

For example,
If initially I had Categories A B C D E F G and I consolidated, I would add all the values in A and, say F, to get a new category.

Another issue is that all these 145 categories are repeated over 60 time periods. So I have to consolidate the data separately for each time period.

To do this, I am trying to use arrays.

Sub CategoriesToSectors()
Dim k As Integer
Dim j As Integer
Dim p As Integer
Dim Destination As Range
' p is just a filler/dummy variable until I later decide which categories go into which    sector 


Dim CategoryData(144, 14) As Long
Dim SectorData(23, 14) As Long

k = 0
' k should go Upto 60
' I first copy the data from a range in the first worksheet into the array CategoryData 
' Then I move 145 rows down for the next time-period's data and repeat this whole process
While k < 60
Sheets("ReformattedData").Select
Range("B1:P145").Select
ActiveCell.CurrentRegion.Offset(k * 145, 0).Select
CategoryData = Selection.Value

For j = 0 To 14
SectorData(0, j) = CategoryData(1, j) + CategoryData(6, j) + CategoryData(8, j) +           CategoryData(13, j)
For p = 1 To 23
SectorData(p, j) = CategoryData(15, j) + CategoryData(19, j) + CategoryData(31, j) +    CategoryData(44, j)
Next p
Next j
' paste consolidated sectordata array one below another in SectorData worksheet
Sheets("SectorData").Select
 Range("B2").Select
Set Destination = ActiveCell.Offset(k * 25, 0)
Destination.Resize(UBound(SectorData, 1), UBound(SectorData, 2)).Value = SectorData


Wend 


End Sub

As you can see, what I am doing is first trying to copy the first range block into the CategoryData array. Then, I am combining the data into the sector array — I have just used repeated values to test it — the for loop with p should not exist. I will eventually use 24 different statements to create the SectorData Array.

Then I paste the Consolidated data onto another sheet. I Go back to the first sheet and move my selection down for the next range block (145 cells below the first cell) then I select this data and repeat.

This does not seem to work — error in entering data into the first array — CategoryData.

Help would be appreciated.

Thank you

VBA Range to 2D Array

These list of topics are covered in this page.

1. Vba Code to initialize 2D Array with Range
2. Vba to write Array data to worksheet
3. Resize Array to fit worksheet.

This code converts worksheet data in specific range into an vba array. Lets see how it works.

1.Excel Macro – Convert Range To Array(2-Dimention)

Actually, it does not require any special function. It is almost easy like assigning a value to a variable.

Once you assign the range to the array, Excel automatically calculates & allocates array dimensions. You don’t have to specify the number of rows, columns or dimensions for the array.

Excel decides these parameters automatically & created a 2D Array by default. It will be clear from below example.

'--------------------------------------------------------------------------------
'Visit https://officetricks.com to get more Free & Fully Functional VBA Codes
'--------------------------------------------------------------------------------
Sub Range_To_Array()
    'Declare Array as variant & Range
    Dim rArray() As Variant, rRange As Range, iRow As Double, iCol As Double
    
    'Initialize Range to Reference a portion of Worksheet
    Set rRange = ThisWorkbook.Sheets("sheet1").Range("A1:D2")
    
    'Assign Range to Array
    rArray = rRange.Value
    
    'Read content of Array one by one
    For iRow = 1 To UBound(rArray, 1)
        For iCol = 1 To UBound(rArray, 2)
            Debug.Print rArray(iRow, iCol)
        Next
    Next           
End Sub

Once you convert the worksheet data to array, you get the advantage of using the Array function on the values. This way the code runs much faster than accessing worksheet data each time.

2. Convert Array to Range – Write to Worksheet

When we write values back to worksheet from array, you have to explicitly mention number of rows & columns the data should occupy.

For this first assign a cell to a range variable: set rng = Thisworkbook.Sheet(1).Range(“A1”) & then use range.resize as explained in this code.

Sub ArrayToRange()
    'Declare Array as variant & Range
    Dim rArray() As Variant, rRange As Range, iRow As Double, iCol As Double
    Set rRange = ThisWorkbook.Sheets("sheet1").Range("A1:D2")
    rArray = rRange.Value
    
           
    'Write values in Array back To a Range in worksheet
    Set rRange = ThisWorkbook.Sheets("sheet1").Range("F1:I2")
    rRange = rArray
    
    'OR Resize Range as per the structure of Array
    iRow = UBound(rArray, 1)
    iCol = UBound(rArray, 2)
    Set rRange = ThisWorkbook.Sheets("sheet1").Range("F1")
    Set rRange = rRange.Resize(iRow, iCol)
    MsgBox rRange.Address(0, 0)
    rRange = rArray
End Sub

To know how Range.resize works, read further in the next section.

3. Excel VBA – Resize Array to fit Worksheet

Once it is initialized, then it can be extended or resized as per the structure of the array.

'Get Number of Rows in Array
rows = UBound(arr,1)

'Get number of columns in Array
cols = UBound(arr,2)

'Resize array as per the array dimensions
set rng = rng.resize(rows,cols)

'Write Array value to the Range
rng = arr

So, the Excel VBA function Range.Resize holds the key here. It can be used to expand or compress the target destination range size.

I have often read that processing values from an array is much faster than processing values from Excel sheet. Though, I haven’t tested this. But, in case you want to move the values from worksheet or Table to an Array, then this code can be used.

External Reference: Here is another wonderful article from cpearson about arrays & worksheets.

I know you can easily take a range of cells and slap them into a Variant Array but I want to work with a string array (because it’s single-dimensional and takes less memory than a Variant array).

Is there any way to automatically convert a range into a string array?

Right now I am using a function that will take the range and save the values in a variant array, then convert the variant array to a string array. It works nice , but I’m looking for a way to go directly from the range to string array. Any help would be greatly appreciated.

Function RangeToArray(ByVal my_range As Range) As String()

Dim vArray As Variant
Dim sArray() As String
Dim i As Long

vArray = my_range.Value
ReDim sArray(1 To UBound(vArray))

For i = 1 To UBound(vArray)
    sArray(i) = vArray(i, 1)
Next

RangeToArray = sArray()

End Function 

UPDATE:
It’s looking like there is no way to skip the step of throwing the data into a variable array first before converting it to a single-dimensional string array. A shame if it’s true (even if it doesn’t take much effort, I like to ultra-optimize so I was hoping there was a way to skip that step). I’ll close the question in a few days if no solution presents itself. Thanks for the helpful comments, guys!

UPDATE2:
Answer goes to Simon who put in great effort (so did everyone else) and utlimately pointed out it’s indeed impossible to go from range to string array in one shot. Thanks, everyone.