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

[QTE]

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.

 

[Norman Jones]

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
..

 

[QTE]

Hi Norman,

and All Forum Users

Thanks for reply. I have implemented your suggestion for a separat
named range object (makes perfect sense: now that you've pointed it ou
of course) SET to the original range that contains the whol
un-filtered list, qualified by .SpecialCell(xlVisible). But the dat
returned is still based on the whole un-filtered list and not th
filtered, Visible Cells only.

With using the newly declared range object: Freq_Results_Filtered, m
worksheet returned #NAME error because that range replaced named rang
Freq_Results. So, I still have named range Freq_Results=whol
un-filtered list, then I created named range Freq_Results_Filtered=th
exact same range as whole un-filtered list, but will be qualified b
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 wan
the Visible Cells. Do I need to write a qualification for Hidde
Cells, i.e. those that become hidden once the data i
filtered?.....Help!

Further assistance or suggestions much appreciated.

Thanks
QTE

 

[Norman Jones]

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.

 

[QTE]

Hi Norman,

Thank you for follow-up. I am actually using the Advanced Filter vi
my macro to first, filter the list in-place, then use the filtered lis
to get the Frequencies (as we know isn't working). As you suggest: i
does appear that the solution may require an additional step/ manua
user intervention which I was trying to avoid. However, if anythin
else should come to mind to keep the process automated (free of use
intervention) please let me know.

Thanks
QT

 

[Norman Jones]

Hi QTE,

I am not, of course, familiar with your application, but why is manual
intervention necessary if that is not what you want? The list can be
filtered to another location automatically with the frequencies being
entered (and reported?) by your routine.

 

[QTE]

Hi Norman,

Yes, you're quite right: no user intervention is required. My thought
were wandering. I've just got a bee in my bonnet about keeping all th
data on one sheet. But hey, that's Excel, I suppose.

Cheers,
QT