MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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


Posted by Roger on March 20, 2001 11:01 PM

I am using the auto filter function to select groups of data. I then have to copy and past that data somewhere else. I would like Excel to automatically select this filtered data. Is there a way to do this? The number of columns is fixed but the number of rows varies. I can't seem to figure out how to automate this part of my spread sheet. I am still new at Excel. Maybe even better yet... can I perform functions on this data? Such as Sum(... or Average(...
The problem is once the rows are selected I may see rows 23,24,45,46,47,78,88 etc. Using Sum(... will summaize 23:88 inclusive. Thanks a million for any help.
Roger


Posted by Dave Hawley on March 21, 2001 12:31 AM


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

Posted by Roger on March 21, 2001 9:48 AM

Excelent! Thank you.

Posted by Roger on March 21, 2001 10:10 AM

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.

Posted by Roger on March 21, 2001 10:20 AM

Problem Solved

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