VBA add to Array with multiple CurrentRegion ranges

[Boffa]

HI Guys,

I know how to add to an array using the Range.CurrentRegion, but what if you have a range of data across multiple sheets ?
Is it possible to loop through each sheet and add to the array using range.CurrentRegion on each sheet, without over-riding what's already existing in the array, but instead just appending ?

Below is my code which is over-riding the data in the array on each loop
Any help much appreciated

  ReDim arr(1 To Sheets.Count - 2, 1 To 5)
    For Each ws In Sheets
        If ws.Tab.Color <> vbBlue Then
           arr = ws.Range("a1").CurrentRegion.Offset(1).Resize(ws.Range("a1").CurrentRegion.rows.Count - 1)
        End If
    Next ws

 

[mohadin]

Hi
An option to use jugged array
EX:

Sub test()
 ReDim arr(1 To Sheets.Count - 2)
 i = 1
    For Each ws In Sheets
        If ws.Tab.Color <> vbBlue Then
           arr(i) = ws.Range("a1").CurrentRegion.Offset(1).Resize(ws.Range("a1").CurrentRegion.Rows.Count - 1)
           i = i + 1
        End If
    Next ws
End Sub