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