Be careful with that firstrow line. Depending on where the visible rows are,
you may not get what you want.

Imagine that row 1 contains the headers and rows 2-99 contain the same value.
When you filter by that value, the first row will be row 2, not the first row of
the second area--heck, there may not even be a second area!

I usually use something like:

With activesheet.AutoFilter.Range
If .Columns(1).Cells.SpecialCells(xlCellTypeVisible). Count = 1 Then
MsgBox "only the headers are visible"
else
'resize to avoid the header
'and come down one row
Set VisRng = .Resize(.Rows.Count - 1).Offset(1, 0) _
.Cells.SpecialCells(xlCellTypeVisible)
End With



rpw wrote:

Cancel this question. Thank you all because I've been able to (finally) find
what I needed from other posts. Here's shortcut version in case anyone is
interested:

Dim ws1 As Worksheet
Dim LastRow As Long, FirstRow As Long

Set ws1 = Worksheets("MySheet")

ws1.Activate
With ws1
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
FirstRow = Cells.SpecialCells(xlCellTypeVisible).Areas(2).Row
End With
MsgBox FirstRow
MsgBox LastRow
--
rpw

"rpw" wrote:

Hello all,

Using Excel 2007. I have a worksheet that I filter and then copy/paste the
results to another worksheet. I have a list of filter criteria and perform a
new filter for each item in the list.

When I apply the first filter, the result is 9 rows - A3:A86. The next
filter I apply will have different results - 5 rows - A17:A345.

My 'stuck point' is programatically identifying the results range
(specifically the row numbers) after the filter is applied. Row 1 is column
headers.

I hope that this is clear enough. Any help is greatly appreciated. Thanks
in advance.
--
rpw

--

Dave Peterson