Extract/Split multiple ranges from Range.Area method when it returns original Range object instead of collection?

Question

I overlooked an obvious case scenario while writing the following, and am hoping the experts here can shed light on what path I should go down next to recover this code...

If Application.counta(wurgtbl.DataBodyRange.Columns(7)) > 0 Then 'checks if Note column contains any

    wurgtbl.DataBodyRange.AutoFilter Field:=7, Criteria1:="=*"  'uses autofilter to show only rows with a note

    Dim noterange As Range 'store note row addresses in range, each row (was supposed to) be treated as separate area
    Set noterange = wurgtbl.DataBodyRange.SpecialCells(xlCellTypeVisible)

    'oops, this only works if 100% discontinuous (multiple selection) ranges.  when any contiguous rows have a note,
    'their ranges are treated as a single selection which breaks my assumption of each row being a separate area!  
    'I had initially spaced my test notes out on different rows and it only broke when testing two contiguous notes

    'add range to array: since discontinuous range is certain, cant mainnotes = noterange.Value, must loop instead
    Dim mainnotes() As String
    ReDim mainnotes(0 To 6, 0 To noterange.Areas.Count - 1)
    Dim area As Range 'each group (area) will get its own row in array
    Dim acell As Range 'this will be each actual cell in the sub-groups
    Dim areaNum As Long

    areaNum = 0
    For Each area In noterange.Areas
        i = 0
        For Each acell In area
            mainnotes(i, areaNum) = acell.Value
            i = i + 1
        Next acell
        areaNum = areaNum + 1
    Next
End If

From Microsoft documentation:

For a single selection, the Areas property returns a collection that contains one object—the original Range object itself. For a multiple-area selection, the Areas property returns a collection that contains one object for each selected area.

Therein is the problem: I assumed Areas property would always return each row as an Area object, but in hindsight it makes sense that it would join the Areas by default if they are contiguous ranges.

The overall goal is to store any notes the user placed in a column before refreshing the workbook data. Once refreshed, it loops through the array and places the notes back into matching entries. I was using hidden helper worksheets (to store) previously but wanted to use arrays instead for all the obvious benefits. My initial assumption of only non-contiguous note rows was admittedly a terrible assumption in hindsight. While the workbook will absolutely have non-contiguous notes, it could also have them contiguous, and will most likely contain both, making noterange return a combination of areas where some areas are individual rows as I want them, but other areas are contiguous rows that need to be split somehow to work with my design.

So is it possible to take a contiguous range and then split it into component rows? For example, the range object noterange.Address returns contiguous:

$A$4:$G$6

But I assumed it would return as a collection (before reading the documentation for Range.Areas)

$A$4:$G$4,$A$5:$G$5,$A$6:$G$6

containing N = 3 areas. (just as an example, in practice N is ~ 140K rows that might potentially contain a value in the note column.)

To be clear, the actual VBA error occurs here mainnotes(i, areaNum) = acell.Value because if an Area contains more than one row, the number of cells (or i) then exceeds the bounds of the array definition. Everything works great if there are no contiguous rows, where it then treats each row as a separate area.

In typing this out, hopefully my subconscious begins to solve it. But I feel I've reached my limit of knowledge on this and would appreciate being steered in the right direction!

Answer 1

Huge credit to BigBen for providing the necessary clues in his comments and introducing me to .CountLarge in an Answer he subsequently deleted.

I implemented most of his suggestions (though column# still hard-coded):

1. Dimensioned the array using standard practice of rows/columns instead of the reverse.
2. Dimensioned the array using noterange.Cells.CountLarge / (# of columns) to get actual number of rows that will exist, rather than using the number of Areas (because some areas will contain more than one row in this scenario.)
3. Iterated through each Range.Area, then each Row within that Range.Area, then through each Cell within that Row, assigning the Range.Value to it's position within the array.

Here is the final code which works no matter what the configuration of the Notes are in the note column:

'check if notes exist in note column
If Application.counta(wurgtbl.DataBodyRange.Columns(7)) > 0 Then 

    'use autofilter to show only rows with a note
    wurgtbl.DataBodyRange.AutoFilter Field:=7, Criteria1:="=*"  

    'store visible addresses in range
    Dim noterange As Range 
    Set noterange = wurgtbl.DataBodyRange.SpecialCells(xlCellTypeVisible)

    'add range to array: since discontinuous range is certain, cant mainnotes = noterange.Value, must loop instead
    Dim mainnotes() As String
    ReDim mainnotes(0 To noterange.Cells.CountLarge / 7 - 1, 0 To 6) 'Credit to BigBen on SO for this (rows/columns)

    Dim areaiterate As Long
    Dim rowNumb As Long
    Dim colNumb as Long
    Dim arow As Range
    Dim rcell As Range

    rowNumb = 0
    For areaiterate = 1 To noterange.Areas.Count
        For Each arow In noterange.Areas(areaiterate).Rows
            colNumb = 0
            For Each rcell In arow.Cells
                mainnotes(rowNumb, colNumb) = rcell.Value
                colNumb = colNumb + 1
            Next
            rowNumb = rowNumb + 1
        Next arow
    Next areaiterate
End If

Thank you to BigBen for the suggestions and snippet!