How to name the range that has been filtered?

A

Amolin

I tend to name the range which obtained by filter, but when I cancel th
filter function, the named range will alter, why, how can I name th
filtered range that coldn't be affected by other Excel manipulation
 
R

Ragdyer

Select the entire filtered list, then <F5>, "Special",
Click on "VisibleCellsOnly", then <OK>.

Now, just click in the name box, and type in your range name, then <Enter>.
 
D

Debra Dalgleish

Since there's a maximum of 255 characters in the name definition, this
technique might not work as expected.

For example, in a list of 100 items, if every second row is filtered
out, the named range might include only the first ten filtered rows.
 
R

RagDyeR

You're right Debra, didn't think of that.

SOooo ... we start off the same way, then, when it comes time to name the
range, we revert to Dave's recommendation of:

Select your range
hit alt-F11 to get to the VBE (where macros live)
hit ctrl-G to see the immediate window
type this and hit enter:

selection.name = "rng"

Back to the worksheet and test it out.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================



Since there's a maximum of 255 characters in the name definition, this
technique might not work as expected.

For example, in a list of 100 items, if every second row is filtered
out, the named range might include only the first ten filtered rows.
 
A

Amolin

Thank you all;

I tried the method you provide, but after I cancel the filter and the
select the range name, it is wrong! I think the filterred data
are from multi rows, so the range name must be a name of multi rows.

Do you have any other good idea
 
R

RagDyeR

You're absolutely right, the named range is for *the* specific filtered
rows.
When you call up the named range, the rows in the data list are selected,
*where ever they are* in the data list.

Maybe ... what you actually want to do, is to *COPY* the filtered list.
Then you can paste it to another location, and keep the rows contiguous.

If that's the case, *forget* about naming the filtered data.
After you select "VisibleCellsOnly" and click <OK>,
Right click in the selection and choose "Copy".

Now, I suggest you go *below* your original data list to do the paste.
This is because the columns are already correctly formatted to match the
original list.

After pasting, you could then name the range of this new filtered list for
quick and easy access (display).

If you do this often, there is a "SelectVisibleCells" icon that you can
place on your toolbar.

Right click in the toolbar and click "Customize".
<Commands> tab, and click "Edit" in the left window.
Scroll almost to the bottom of the right window, and click and drag the
"SelectVisibleCells" icon to your toolbar.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Thank you all;

I tried the method you provide, but after I cancel the filter and then
select the range name, it is wrong! I think the filterred data
are from multi rows, so the range name must be a name of multi rows.

Do you have any other good idea?
 
A

AlfD

Hi!

View> Custom Views offers a way of returning to a given configuration
Is that any use to you?

Al
 
Top