Try something like this.
The three sheet counts are in the N array; N(1), N(2), N(3)
The three row arrays are in the I array (an array of arrays); I(1), I(2), I(3)
Dim N(1 To 3) As Long, I(1 To 3) As Variant, ii As Variant
Dim j As Long, k As Long, ws As Worksheet, cell As Range
For Each ws In Sheets(Array("Sheet1", "Sheet2", "Sheet3"))
With ws.Range("D1", ws.Range("D" & Rows.Count).End(xlUp))
j = j + 1
k = 0
ReDim ii(1 To .Count)
For Each cell In .Cells
If cell.Value <> "" Then
k = k + 1
ii(k) = cell.Row
End If
Next cell
N(j) = k
ReDim Preserve ii(1 To k)
I(j) = ii
End With
Next ws
Bookmarks