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).Sele ct
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/

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 " wrote in message
...
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).Sele ct
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/

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 wrote:
*Hi QTE,

My code returns the values for the whole list, instead of jus
the
Filtered Visible
cells of the Frequency Results(Freq_Results).

This is because it appears that you are using the named rang
Freq_Results
(which corresponds to the UN-filtered list) in your formula.

Set an object variable to the filtered cells and use this in you
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 " wrote in message
...
Data is filtered in-place: I then need to perform a calculation o
the
Visible Filtered cells using the Frequency function. My cod
returns
the values for the whole list, instead of just the Filtere
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).Sele ct
Range("Frequencies").Select
Selection.FormulaArray = ("=Frequency(Freq_Results,Bins)")

Excel Help: -
Frequency Function Overview:
Returns a frequency distribution as a vertical array. For a give
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 whic
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 yo
want
to group the values in data_array. If bins_array contains n
values,
FREQUENCY returns the number of elements in data_array.

Remarks
FREQUENCY is entered as an array formula after selecting a rang
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/

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 " wrote in message
...
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
worksheet 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 " wrote in message
...
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).Sele ct
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/

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

--
Message posted from http://www.ExcelForum.com

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.

---
Regards,
Norman.

"QTE " wrote in message
...
Hi Norman,

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

Thanks
QTE


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com