Referencing Cells in Multi-Area Range?

[Guest]

Good evening,

I use a filter to display selected subsets of rows in a large sreadsheet:

Dim r1 As Range

With xlSheet.Rows
.AutoFilter field:=1, Criteria1:=State
.AutoFilter field:=2, Criteria1:=Evt
Set r1 = .SpecialCells(xlCellTypeVisible)
End With

The spreadsheet shows exactly what I want, but r1 turns out to be a
multi-area range rather than a simple, single area rectabgle.

I'd love to be able to reference cells in r1 with something simple like:

r1.cells(1,2) or r1.cells(5,3)

but, that returns cells from the worksheet that are hidden, sort of "in
between" r1 areas. Items(n,m) doesn't work either. I've tried variations
other than specialcells, too.

Is there a simple way to reference r1 cells by row and column as they appear
on the screen?

I realize that I can loop through the areas, rows, and columns and build my
own 2D matrix, but I suspect there's an easy way to do this that I just
haven't uncovered.

Thanks in advance

Ed

 

[Guest]

This may be a case of bludgeoning the problem to death, but I came up with a
reasonable solution to simple indexing of a multi-area range.

First I declared a new class, fliteredRange, with:

Public fRange As Excel.Range

Function fCells(ByVal r As Long, ByVal c As Long) As Range
Dim rSub As Range

For Each rSub In fRange.Areas
If r > 0 And r <= rSub.Rows.Count Then
Set fCells = rSub.Cells(r, c)
Exit Function
End If
r = r - rSub.Rows.Count
Next rSub
set fCells = Nothing
End Function

Then I set a filteredRange object to the autofiltered cells:

Dim thing as new filteredRange
Set thing.fRange = xlSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible)

and index it thusly:

thing.fCells(1,2)

It needs a little more error checking, but that's the essence.

Ed