Combine smaller arrays to create 1 single big array of data

[kumarcoolz]

Hello Excel community



Regards
Kumar

 

[kumarcoolz]

I am working a excel with 4 sheets... they have many rows of data but the number of columns are the same. So my intension was to use array function and combine all functions into a single 2 Dimensional array. This is the first time I am using arrays. I don't understand the way arrays work

Sub OTDarray()
Dim SE() As Variant
Dim LE() As Variant
Dim MRO() As Variant
Dim RE() As Variant
Dim Ach() As Variant
Sheet3.Activate
SE = Sheet3.Range(Range("A1").End(xlDown), Range("A1").End(xlToRight))
Sheet4.Activate
LE = Sheet4.Range(Range("A1").End(xlDown), Range("A1").End(xlToRight))
Sheet5.Activate
MRO = Sheet5.Range(Range("A1").End(xlDown), Range("A1").End(xlToRight))
Sheet6.Activate
RE = Sheet6.Range(Range("A1").End(xlDown), Range("A1").End(xlToRight))
ReDim Ach(1 To UBound(SE, 1) + UBound(LE, 1) + UBound(MRO, 1) + UBound(RE, 1), 1 To UBound(SE, 2))
Dim i, j As Integer
For i = 1 To UBound(SE, 1)
    For j = 1 To UBound(SE, 2)
        Ach(i, j) = SE(i, j)
    Next j
Next i
Dim k, l As Integer
For i = 1 To UBound(SE, 1)
    For j = 1 To UBound(SE, 2)
        Ach(i, j) = SE(i, j)
    Next j
Next i

'Dim m, n, o, p As Integer
'For m = UBound(SE, 1) + 1 To UBound(SE, 1) + 1 + UBound(LE, 1)
'    For n = 1 To UBound(LE, 2)
'        For o = 1 To UBound(LE, 1)
'            For p = 1 To UBound(LE, 2)
'            Ach(m, n) = SE(o, p)
'
'            Next p
'        Next o
'   Next n
'Next m

Erase SE
Erase LE
Erase MRO
Erase RE
Erase Ach
End Sub

This is code that I used.

It would be great if someone to correct me in my logic or suggest better method

 

[JackDanIce]

It's not clear what you're doing with the array after you collect the data, but I suggest using a temporary sheet to write all the data to it and then read it into a single array, try:

Sub OTDarray()

    Dim x       As Long
    Dim y       As Long
    Dim wksTemp As Worksheet
    Dim var     As Variant
    Dim arr()   As Variant
    
    Application.ScreenUpdating = False
    
    Set wksTemp = Worksheets(Worksheets.count).add
    
    For Each var In Array(Sheet3, Sheet4, Sheet5, Sheet6)
        With var
            x = .Cells(.Rows.count, 1).End(xlUp).row
            y = .Cells(1, .Columns.count).End(xlToLeft).column
            arr = .Cells(1, 1).Resize(x, y).Value
            wksTemp.Cells(Rows.count, 1).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
            Erase arr
        End With
    Next var
    
    With wksTemp
        x = .Cells(.Rows.count, 1).End(xlUp).row
        y = .Cells(2, .Columns.count).End(xlToLeft).column
        'Single array with data from Sheet3, Sheet4, Sheet5 and Sheet6
        arr = .Cells(2, 1).Resize(x - 1, y).Value
    End With
    
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
       'Delete temp worksheet
        wksTemp.Delete
        .DisplayAlerts = True
    End With
    
    Set wksTemp = Nothing
    Erase arr
    
End Sub

 

[kumarcoolz]

Thanks JackDanIce

It works perfectly..... I just wanted to understand the logic to accomplish

I have been using long For Loops to combine many sheets to single worksheet..... I wanted to try out with Arrays as I have been told arrays work faster but I have not idea on arrays



Thanks
Palani

 

[JackDanIce]

You're welcome. See comments in code and you'll need to read about arrays in VBA, internet search has lots of links:

Sub OTDarray()

    Dim x       As Long
    Dim y       As Long
    Dim wksTemp As Worksheet
    Dim var     As Variant
    Dim arr()   As Variant
    
    Application.ScreenUpdating = False
    
    'Add a new temporary sheet to build data up with
    Set wksTemp = Worksheets(Worksheets.count).add
    
    'Loop over the worksheet objects
    For Each var In Array(Sheet3, Sheet4, Sheet5, Sheet6)
        With var
            'Determine the last used row on column A
            x = .Cells(.Rows.count, 1).End(xlUp).row
            'Determine the last used column on row 1
            y = .Cells(1, .Columns.count).End(xlToLeft).column
            'Read data into an array from A1 to last row and last column
            arr = .Cells(1, 1).Resize(x, y).Value
            'Write data to temporary sheet array by array
            wksTemp.Cells(Rows.count, 1).End(xlUp).Offset(1).Resize(UBound(arr, 1), UBound(arr, 2)).Value = arr
            'Clear array
            Erase arr
        End With
    Next var
    
    With wksTemp
        'Determine row and cell based on column A and row 1 respectively
        x = .Cells(.Rows.count, 1).End(xlUp).row
        y = .Cells(2, .Columns.count).End(xlToLeft).column
        'Single array with data from Sheet3, Sheet4, Sheet5 and Sheet6
        'Read all data into a single array
        arr = .Cells(2, 1).Resize(x - 1, y).Value
        'Delete temp sheet, without warning message
        Application.DisplayAlerts = False
        .Delete
        Application.DisplayAlerts = True
    End With
    
    Application.ScreenUpdating = False
    
    'Clear variables
    Set wksTemp = Nothing
    Erase arr
    
End Sub