Create a Range Object from Multiple Areas Items

Question

I have a function that aims to return the visible cells (as a range) after applying an autofilter to an inactive worksheet; the autofilter data is represented by the range "filteredData" passed to the function. The returned range can then be looped through by the calling code obtaining various values from the nth row.

I now understand that if the filtered data contains non-contiguous row groupings, only the first group of those rows is returned as a range, using .SpecialCells(xlCellTypeVisible), and that each of those non-contiguous row groupings is represented by an item, all contained by the same, single Areas collection - I think.

Is it possible to "convert" those area items into an overall range object? I have tried using the Address property of the item and UNION, but this only seems to work for the first area item and seems to fail silently when attempting to add a second; no error occurs, but the row count of the newRange remains unchanged.

Several other scripts are tied into this function and I would like to try to avoid a large re-write.

Any advice would be appreciated.

Thanks

Function getFilteredData(filteredData As Range) As Range
Dim areasData As Range
Dim areaCount As Long
Dim j As Long
Dim areaRg As Range
Dim sheetName As String
Dim newRange As Range
Dim itemAddress AS String
Dim itemRg AS Range

Set areasData = filteredData.Resize(filteredData.Rows.Count - 1, filteredData.Columns.Count).Offset(1).SpecialCells(xlCellTypeVisible)

sheetName = "'" & filteredData.Parent.Name & "'!"
areaCount = areasData.Areas.Count

For j = 1 To areaCount

    'unsure if this can be treated as a range...possibly Area object
    Set areaRg = areasData.Areas.item(j)
    itemAddress = sheetName & areaRg.CurrentRegion.Address
    Set itemRg = Range(itemAddress)

    If j = 1 Then
        Set newRange = itemRg
    Else
        Set newRange = Union(newRange, itemRg)
    End If

Next j

Set getFilteredData = newRange

End Function

Answer 1

Given your use case of looping through the nth row you could use a utility function, e.g.

Function getRangeRowNum(data As Range, num As Long) As Range
    If num < 1 Then num = 1
    If data.Areas.Count = 1 Then
        If num > data.Rows.Count Then
            Set getRangeRowNum = data.Rows(data.Rows.Count)
        Else
            Set getRangeRowNum = data.Rows(num)
        End If
        Exit Function
    End If
    Dim i As Long, runRows As Long
    For i = 1 To data.Areas.Count
            runRows = runRows + data.Areas(i).Rows.Count
            If runRows >= num Then Exit For
    Next i
    If i > data.Areas.Count Then    'Exit For not executed so return last actual row'
        Set getRangeRowNum = data.Areas(i - 1).Rows(data.Areas(i - 1).Rows.Count)
    Else
        Set getRangeRowNum = data.Areas(i).Rows(num - (runRows - data.Areas(i).Rows.Count))
    End If
End Function

Sub testFunction()
    Dim i As Long, total As Range
    Set total = Application.Union(Range("A5:H6"), Range("A9:H12"), Range("A15:H18"))
    Debug.Print "Rows property of 'Total' returns " & total.Rows.Count
    Debug.Print "Actual number of rows in 'Total' = " & total.Cells.Count / total.Columns.Count
    For i = 1 To 10
        Debug.Print getRangeRowNum(total, i).Address
    Next i
End Sub

If you do intend to continue with filtering ranges though, I think some re-factoring will be inevitable.

An alternative you might consider is to pass an array between your various scripts, since an array is a properly contiguous structure.

Such an array could be constructed from an ADODB.Recordset object, e.g. this video illustrates data being read into one from a closed workbook (although it works just as well for the workbook you have open, provided all changes are saved, since it is the 'disk copy' that is queried).

With this approach you define your filter in SQL (so it could actually be more sophisticated than with AutoFilter), and the filtered results are what are read into the Recordset. The video illustrates getting data 'out of' the RecordSet, but this one shows how you can also transfer it to an array (although you probably will want to use Application.Transpose to have it in the expected form - you should also test thoroughly if you have long ranges, as Transpose didn't always work with more than 65,536 items).

This snippet illustrates how you can also create a RecordSet directly from a range, although I don't know that it's as efficient as the SQL approach. Regardless of how you populate the RecordSet, it has Filter and Sort properties (illustrated in the previous link) such that you can continue to manipulate the data directly in memory before generating the array required.