Dynamic Ranges - Create Using VBA as looping through Sheets

Hello,

I’m attempting to grasp the concepts of working with arrays.

• Is it possible to loop through a series of worksheets containing the same types of data
• Select all the data on each sheet
• Declare the data as a range dynamically ( if this is necessary)
• Load that range into an array (nRows, 5Col)
• And then perform actions based upon items in that array?

Sub TestArray()

Dim SheetData As Variant
Dim lngRow As Long
Dim lngCol As Long
Dim SrcRng As Range

SheetData = ThisWorkbook.Names("SrcRng").RefersToRange
    For lngRow = LBound(SheetData) To UBound(SheetData)
    For lngCol = LBound(SheetData, 2) To UBound(SheetData, 2)
    
 Debug.Print "Item ("; lngRow; ","; lngCol; ")", SheetData(lngRow, lngCol)
    
    Next
    Next
Debug.Print

End Sub

I’ve attached a workbook with sample data and the code that I’ve managed to cobble together at this point.
It doesn't loop through the worksheets nor dynamically create the data ranges.

Thanks for your assistance.
Mark

I wrote the attached code for another thread. It gives you some direction. I have put comment on each line. No every single line may be relevant to your case, it is just a guide.

Thanks a lot AB.
It's an elegant piece of code.

I had a problem with a "Subscript out of range" on this line.
I commented out that particular "If" statement and the code ran fine.

 Sub ConsolidateData()
Dim ws As Worksheet, wsMaster As Worksheet, x, z, i As Long, ii&, k&
'Application.ScreenUpdating = 0
Set wsMaster = Sheets("Master")
  x = Range("A1:E" & Cells.Find("*", , , , xlByRows, xlPrevious).Row) 'Load the data in to an array
     ReDim z(1 To UBound(x) * Sheets.Count - 1, 1 To UBound(x, 2)) 'output array
 For Each ws In Worksheets  'Loop through each sheet
    If ws.Name <> wsMaster.Name Then  ' if the sheet name is not "Master"
        x = ws.Range("A1:E" & ws.Cells.Find("*", , , , xlByRows, xlPrevious).Row)
        For i = 2 To UBound(x, 1)  'Loop through each array for each sheet at a time.
             If Trim((x(i, 7))) = vbNullString Then
            
                 k = k + 1
                For ii = 1 To UBound(x, 2) ' If the condition is true,loop through columns and copy data in to output array
                    z(k, ii) = x(i, ii)
                Next
             End If
        Next i
    End If
Next ws
 With wsMaster
      .UsedRange.Offset(1).ClearContents
      .Range("A1").Resize(k, UBound(x, 2)) = z ' The adjusted array is copied back in to range.
      .Columns.AutoFit
 End With
End Sub

I was wondering what the intention was for this bit of code?
When I changed the number from 7 to 5, I received the value of the date in column five.
The problem was, it wouldn't output at all to the Master Worksheet.

I'd be interested in understanding the mechanics of this.

Thanks again.

If Trim((x(i, 7))) = vbNullString Then

Simply testing if column G is nil. It is one of the condition for that particular task.

The reason forgetting the error is this line.

 x = ws.Range("A1:E" & ws.Cells.Find("*", , , , xlByRows, xlPrevious).Row)

The max column loop the X array can take is 5, which is E. If you change it to G, you would not get an error.

Thanks for the explanation.
I didn't think to look back in the code to find the problem.
A very useful bit of information.

Thanks again.

I was wondering what the intention was for this bit of code?

The person who asked the question wants to copy all columns if column E is blank. So, the condition is "If statement". Not all rows in column E are blank.