Combine smaller arrays to create 1 single big array of data

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

kumarcoolz

New Member
Joined
Mar 24, 2013
Messages
7
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


Code:
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,671
Office Version
365
Platform
Windows
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:
Rich (BB code):
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
 
Last edited:

kumarcoolz

New Member
Joined
Mar 24, 2013
Messages
7
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

Well-known Member
Joined
Feb 3, 2010
Messages
9,671
Office Version
365
Platform
Windows
You're welcome. See comments in code and you'll need to read about arrays in VBA, internet search has lots of links:
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,460
Messages
5,468,775
Members
406,608
Latest member
G3TEN

This Week's Hot Topics

Top