Hi Mark,
It is not immeditely apparent what you are trying to do.
If you are trying to reassure yourself that the correct range is being
addressed, then a simple
Msgbox FilteredRange .Address
may suffice.
Your attempt to reference individual cells of the autofiltered range will
not work - as you have discovered. The reason for this is that , for
example,
FilteredRange(3,3)
refers to a cell two rows down and two rows to the right of the first cell
in the autofilter range and this cell may (or may not!) be in the autofilter
range. Also FilteredRange(1,1)
refers to the first header cell.
Again as you have dicovered, you should not count the number of rows in the
autofiltered range with the statement:
FilteredRange.Rows.Count
This is because typically the autofilter range is compised of a nummber of
discontiguous areas and , in such cases, the Rows.Count will return thr
number of rows in the first area of the autofiltered range. If you need the
row count of the range, you would need to build it with a counter.
The following exemplifies a method for returning the autofilter range
address and row count via messagebox alerts and returns the individual
filtered cell values (row by row to the immediate window.
Sub Tester2()
Dim FilteredRange As Range, rw As Range
Dim iCtr As Long, j As Long
Set FilteredRange = Worksheets("tblInt9").AutoFilter.Range. _
SpecialCells(xlCellTypeVisible)
j = FilteredRange.Columns.Column
For Each rw In FilteredRange.Rows
If rw.Row > FilteredRange.Rows.Row Then
iCtr = iCtr + 1
Debug.Print Cells(rw.Row, j).Value _
& " " & Cells(rw.Row, j + 1) _
& " " & Cells(rw.Row, j + 2) _
& " " & Cells(rw.Row, j + 3)
End If
Next
MsgBox FilteredRange.Address
MsgBox "The number of autofiltered rows is " & iCtr
End Sub