Re: Excel Macro Filtered Data in-place: cannot calculate Frequency of Visible Cells

From: Norman Jones (normanjones_at_whereforartthou.com)
Date: 06/03/04 

Date: Fri, 4 Jun 2004 00:35:03 +0100

Hi QTE,

Please ignore my previous response and my apologies for misleading you.

Starting again!

You cannot use the Frequncies function on an autofiltered range - unlike the
Subtotal function, it cannot distinguish between visible and hidden rows.

What you could do, perhaps, is to use the Advanced Filter, set a criteria
range where you (your user?) can input your filter conditions, and then use
the Filter output range as the first argument in your frequencies formula.
Since the frequencies function ignores blank cells, just set your output
range to the present (future?) size of your unfiltered data range. 

---
Regards,
Norman
"QTE >" <<QTE.17a8bi@excelforum-nospam.com> wrote in message
news:QTE.17a8bi@excelforum-nospam.com...
> Hi Norman,
>
> and All Forum Users
>
> Thanks for reply.  I have implemented your suggestion for a separate
> named range object (makes perfect sense: now that you've pointed it out
> of course) SET to the original range that contains the whole
> un-filtered list, qualified by .SpecialCell(xlVisible).  But the data
> returned is still based on the whole un-filtered list and not the
> filtered, Visible Cells only.
>
> With using the newly declared range object: Freq_Results_Filtered, my
> work*** returned #NAME error because that range replaced named range
> Freq_Results.  So, I still have named range Freq_Results=whole
> un-filtered list, then I created named range Freq_Results_Filtered=the
> exact same range as whole un-filtered list, but will be qualified by
> the SET statement to operate on Visible Cells only:
>
> Set Freq_Results_Filtered =
> Range("Freq_Results").SpecialCells(xlVisible)
>
> Is my above scenario and application of the named ranges logical?
>
> My macro does not recognise the SpecialCells method?
>
> Syntax 1-
> Set Freq_Results_Filtered =
> Range("Freq_Results").SpecialCells(xlVisible)
> Range("Frequencies").Select
> Selection.FormulaArray =
> ("=Frequency(Freq_Results_Filtered,Bins)")
>
> Syntax 2-
> Set Freq_Results_Filtered =
> Range("Freq_Results").SpecialCells(xlVisible)
> Range("Frequencies").FormulaArray =
> ("=Frequency(Freq_Results_Filtered,Bins)")
>
> Both syntax's produce the frequencies for the "whole list", I only want
> the Visible Cells.  Do I need to write a qualification for Hidden
> Cells, i.e. those that become hidden once the data is
> filtered?.....Help!
>
> Further assistance or suggestions much appreciated.
>
> Thanks
> QTE
>
> Norman Jones wrote:
> > *Hi QTE,
> >
> > > My code returns  the values for the whole list, instead of just
> > the
> > Filtered Visible
> > > cells of the Frequency Results(Freq_Results).
> >
> > This is because it appears that you  are using the named range
> > Freq_Results
> > (which corresponds to the UN-filtered list) in your formula.
> >
> > Set  an object variable to the filtered cells and use this in your
> > formula
> > in place of Freq_Results, e.g something along the lines of:
> >
> > Dim Your existing variables
> > Dim Rng as range
> >
> > On Error Resume Next
> > Set  Rng = Range("Freq_Results").SpecialCells(xlVisible)
> > On Error GoTo 0
> > ' your other code
> >
> > Range("Frequencies").FormulaArray = ("=Frequency(Rng,Bins)")
> > 'remaining code
> > ..
> > ---
> > Regards,
> > Norman
> >
> >
> > "QTE >" <<QTE.17952z@excelforum-nospam.com> wrote in message
> > news:QTE.17952z@excelforum-nospam.com...
> > > Data is filtered in-place: I then need to perform a calculation on
> > the
> > > Visible Filtered cells using the Frequency function. My code
> > returns
> > > the values for the whole list, instead of just the Filtered
> > Visible
> > > cells of the Frequency Results(Freq_Results).
> > >
> > > Freq_Results=Named Range of All Unfiltered Frequency Results, then
> > > filtered in_place
> > >
> > > Frequencies=Named Range Number of Times
> > >
> > > Bins=Named Range of Intervals
> > >
> > > Range("Freq_Results").SpecialCells(xlVisible).Select
> > > Range("Frequencies").Select
> > > Selection.FormulaArray = ("=Frequency(Freq_Results,Bins)")
> > >
> > > Excel Help: -
> > > Frequency Function Overview:
> > > Returns a frequency distribution as a vertical array. For a given
> > set
> > > of values and a given set of bins (or intervals), a frequency
> > > distribution counts how many of the values occur in each interval.
> > >
> > > Syntax:
> > > FREQUENCY(data_array, bins_array)
> > >
> > > Data_array is an array of or reference to a set of values for which
> > you
> > > want to count frequencies. If data_array contains no values,
> > FREQUENCY
> > > returns an array of zeros.
> > > Bins_array is an array of or reference to intervals into which you
> > want
> > > to group the values in data_array. If bins_array contains no
> > values,
> > > FREQUENCY returns the number of elements in data_array.
> > >
> > > Remarks
> > > FREQUENCY is entered as an array formula after selecting a range
> > of
> > > adjacent cells into which you want the returned distribution to
> > > appear.
> > > The number of elements in the returned array is one more than the
> > > number of elements in bins_array.
> > > FREQUENCY ignores blank cells and text.
> > > Formulas that return arrays must be entered as array formulas
> > >
> > > Suggestions, Please.
> > >
> > >
> > > ---
> > > Message posted from http://www.ExcelForum.com/
> > > **All Forum Users*
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>