How do I automatically select an "auto filtered" range???

Hi Roger

In answer to your first question you can certainly define the filered range by using SpecialCells:

Sub SetFilteredRange()
Dim FilterRange As Range

'Offset 1 Row to exclude headings
Set FilterRange = ActiveSheet.UsedRange.Offset(1, 0) _
.SpecialCells(xlCellTypeVisible)

FilterRange.Copy Destination:=Sheets("Sheet2").Range("A1")

End Sub

Now, there is no real need to actually do this although it can make your code easier to decipher later on. When you copy a filetered range, Excel by default will only copy the VISIBLE cells. So you could simply use:

Sub CopyFilteredRange()

ActiveSheet.UsedRange.Offset(1, 0).Copy _
Destination:=Sheets("Sheet2").Range("A1")

End Sub

Although as mentioned above this does not actually tell you the range is filtered, so if you use this method a comment is good practice.

To sum your filetered range again there are 2 methods you can use:

Sub SumFilteredRange1()
Dim FilterRange As Range
Dim SummedRange As Long

Set FilterRange = ActiveSheet.UsedRange.Offset(1, 0) _
.SpecialCells(xlCellTypeVisible)

SummedRange = WorksheetFunction.Sum(FilterRange)
MsgBox SummedRange
End Sub

....Or you can use the SUBTOTAL function, like below:

Sub SumFilteredRange2()
Dim FilterRange As Range
Dim SummedRange As Long

Set FilterRange = ActiveSheet.UsedRange.Offset(1, 0)

SummedRange = WorksheetFunction.Subtotal(9, FilterRange)
MsgBox SummedRange
End Sub

SUBTOTAL will only sum unfiltered cells. The number 9 represents SUM. The others are below.

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

Dave

OzGrid Business Applications

Excelent! Thank you.

Oops - One little problem yet

It works for the most part but I need it to know where to start. The pane is frozen to row 16. The first row of the filtered set is always different. How can I code it so the Sub knows where to start? I filter the data that I need and click a button. All the filtered data is below line 16. Do I select that row, move the cursor down one .Select then run your sub???
Thanks again.

Problem Solved

Sorry - that was a bit of a no brainer - Changed the Offset to 16,0 all is well.