Selecting a filtered range 
Author Message
 Selecting a filtered range

I would like to select (copy) the results of a filtered
range without selecting the entire original range.  There
does not seem to be such a thing as ".Autofilter.Range" that
would be the current filtered range in a worksheet.

Thanks,
Allan Seidel



Wed, 18 Jun 1902 08:00:00 GMT  
 Selecting a filtered range

? ActiveSheet.Autofilter.Range.Address
$A$1:$B$9
? activesheet.Autofilter.Range.SpecialCells(xlvisible).Address
$A$1:$B$1,$A$4:$B$4,$A$6:$B$6,$A$8:$B$9

However, copying the entire range results in only the visible rows being
copied - that is the default behavior.

Regards,
Tom Ogilvy


Quote:
> I would like to select (copy) the results of a filtered
> range without selecting the entire original range.  There
> does not seem to be such a thing as ".Autofilter.Range" that
> would be the current filtered range in a worksheet.

> Thanks,
> Allan Seidel



Wed, 18 Jun 1902 08:00:00 GMT  
 Selecting a filtered range

hi there,

I have been using something like this to copy the filtered rows:

ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Select
Select.Copy
' and paste it where you want
' I create a new workbook, paste the filtered rows and save it.

BUT one thing I dont seem to get is this:

??? ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Rows.Count ???

This is returning the total number of rows, instead of returning number of
rows after the filter.
I need to know this number because I will be creating a new workbook to save
the filtered rows and
I do not want to create a new workbook if no rows returned after the filter.

Can anybody help me with this.

Thanks for you time.


Quote:
> ? ActiveSheet.Autofilter.Range.Address
> $A$1:$B$9
> ? activesheet.Autofilter.Range.SpecialCells(xlvisible).Address
> $A$1:$B$1,$A$4:$B$4,$A$6:$B$6,$A$8:$B$9

> However, copying the entire range results in only the visible rows being
> copied - that is the default behavior.

> Regards,
> Tom Ogilvy



> > I would like to select (copy) the results of a filtered
> > range without selecting the entire original range.  There
> > does not seem to be such a thing as ".Autofilter.Range" that
> > would be the current filtered range in a worksheet.

> > Thanks,
> > Allan Seidel



Wed, 18 Jun 1902 08:00:00 GMT  
 Selecting a filtered range

hi there,

I have been using something like this to copy the filtered rows:

ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Select
Select.Copy
' and paste it where you want
' I create a new workbook, paste the filtered rows and save it.

BUT one thing I dont seem to get is this:

??? ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Rows.Count ???

This is returning the total number of rows, instead of returning number of
rows after the filter.
I need to know this number because I will be creating a new workbook to save
the filtered rows and
I do not want to create a new workbook if no rows returned after the filter.

Can anybody help me with this.

Thanks for you time.


Quote:
> ? ActiveSheet.Autofilter.Range.Address
> $A$1:$B$9
> ? activesheet.Autofilter.Range.SpecialCells(xlvisible).Address
> $A$1:$B$1,$A$4:$B$4,$A$6:$B$6,$A$8:$B$9

> However, copying the entire range results in only the visible rows being
> copied - that is the default behavior.

> Regards,
> Tom Ogilvy



> > I would like to select (copy) the results of a filtered
> > range without selecting the entire original range.  There
> > does not seem to be such a thing as ".Autofilter.Range" that
> > would be the current filtered range in a worksheet.

> > Thanks,
> > Allan Seidel



Wed, 18 Jun 1902 08:00:00 GMT  
 Selecting a filtered range
The range you refer to always refers to the entire range - not just the
visible rows.  It works for copying because the default behaivor of excel is
to only copy the visible rows when passed the entire filtered range.  To get
the count of visible rows

?
ActiveWorkbook.Names("_FilterDatabase").RefersToRange.columns(1).specialCell
s(xlVisible).count
 8

This counts the header row, so if it returns 1, nothing met the filter
criteria.

You also might want to look at
ActiveSheet.Autofilter.Range

as in:

? ActiveSheet.Autofilter.Range.columns(1).specialCells(xlVisible).count
 8

Regards,
Tom Ogilvy

Quote:

> hi there,

> I have been using something like this to copy the filtered rows:

> ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Select
> Select.Copy
> ' and paste it where you want
> ' I create a new workbook, paste the filtered rows and save it.

> BUT one thing I dont seem to get is this:

> ??? ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Rows.Count ???

> This is returning the total number of rows, instead of returning number of
> rows after the filter.
> I need to know this number because I will be creating a new workbook to
save
> the filtered rows and
> I do not want to create a new workbook if no rows returned after the
filter.

> Can anybody help me with this.

> Thanks for you time.



> > ? ActiveSheet.Autofilter.Range.Address
> > $A$1:$B$9
> > ? activesheet.Autofilter.Range.SpecialCells(xlvisible).Address
> > $A$1:$B$1,$A$4:$B$4,$A$6:$B$6,$A$8:$B$9

> > However, copying the entire range results in only the visible rows being
> > copied - that is the default behavior.

> > Regards,
> > Tom Ogilvy



> > > I would like to select (copy) the results of a filtered
> > > range without selecting the entire original range.  There
> > > does not seem to be such a thing as ".Autofilter.Range" that
> > > would be the current filtered range in a worksheet.

> > > Thanks,
> > > Allan Seidel



Mon, 14 Apr 2003 20:44:42 GMT  
 Selecting a filtered range

Just to add.  I once used SpecialCells.  However, after much testing, I have
found the Subtotal is surprisingly faster.
Therefore, I use something along these lines.  Just sharing an idea.  Good
luck.  Dana DeLouis.

  If Not ActiveSheet.AutoFilterMode Then
    Records_Total = 0
    Records_Found = 0
    MsgBox "AutoFilter must be On !"
  Else
    With Range("_FilterDatabase")
      Records_Total = .Rows.Count - 1
      Records_Found = WorksheetFunction.Subtotal(3, .Columns(1)) - 1
    End With
  End If

Quote:

> hi there,

> I have been using something like this to copy the filtered rows:

> ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Select
> Select.Copy
> ' and paste it where you want
> ' I create a new workbook, paste the filtered rows and save it.

> BUT one thing I dont seem to get is this:

> ??? ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Rows.Count ???

> This is returning the total number of rows, instead of returning number of
> rows after the filter.
> I need to know this number because I will be creating a new workbook to
save
> the filtered rows and
> I do not want to create a new workbook if no rows returned after the
filter.

> Can anybody help me with this.

<snip>


Tue, 15 Apr 2003 13:36:34 GMT  
 Selecting a filtered range
Tom,

Thanks for the original answer, however I found, (perhaps erroneously since I
tried it once and then moved on to the next idea) that using the copy function
(within a macro) on the filtered range actually copied the entire range and not
just the visible items.  In other words it seems to work manually but not within
a macro.   Therefore I used the
"Activesheet.Autofilter.Range.SpecialCells(xlvisible)" as you suggested to do
what I wanted.

Thanks again,

Allan Seidel

Quote:

> The range you refer to always refers to the entire range - not just the
> visible rows.  It works for copying because the default behaivor of excel is
> to only copy the visible rows when passed the entire filtered range.  To get
> the count of visible rows

> ?
> ActiveWorkbook.Names("_FilterDatabase").RefersToRange.columns(1).specialCell
> s(xlVisible).count
>  8

> This counts the header row, so if it returns 1, nothing met the filter
> criteria.

> You also might want to look at
> ActiveSheet.Autofilter.Range

> as in:

> ? ActiveSheet.Autofilter.Range.columns(1).specialCells(xlVisible).count
>  8

> Regards,
> Tom Ogilvy


> > hi there,

> > I have been using something like this to copy the filtered rows:

> > ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Select
> > Select.Copy
> > ' and paste it where you want
> > ' I create a new workbook, paste the filtered rows and save it.

> > BUT one thing I dont seem to get is this:

> > ??? ActiveWorkbook.Names("_FilterDatabase").RefersToRange.Rows.Count ???

> > This is returning the total number of rows, instead of returning number of
> > rows after the filter.
> > I need to know this number because I will be creating a new workbook to
> save
> > the filtered rows and
> > I do not want to create a new workbook if no rows returned after the
> filter.

> > Can anybody help me with this.

> > Thanks for you time.



> > > ? ActiveSheet.Autofilter.Range.Address
> > > $A$1:$B$9
> > > ? activesheet.Autofilter.Range.SpecialCells(xlvisible).Address
> > > $A$1:$B$1,$A$4:$B$4,$A$6:$B$6,$A$8:$B$9

> > > However, copying the entire range results in only the visible rows being
> > > copied - that is the default behavior.

> > > Regards,
> > > Tom Ogilvy



> > > > I would like to select (copy) the results of a filtered
> > > > range without selecting the entire original range.  There
> > > > does not seem to be such a thing as ".Autofilter.Range" that
> > > > would be the current filtered range in a worksheet.

> > > > Thanks,
> > > > Allan Seidel



Tue, 15 Apr 2003 23:07:25 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Selecting a filtered range

2. select data range without using filter

3. Select query filtering by date range.

4. Select a Range, deselect bottom row of range

5. VBA :Selecting ranges that vary, Naming ranges based on Formatting

6. Select range within range

7. Select Range in other range

8. XL97: selecting range relative to other range

9. prompting user to select a range and using this range

10. Selecting a Range of Cells, with Unknown Rows in Range


 
Powered by phpBB® Forum Software © phpBB Group