non-contiguous named range - how to use in formulas, or how to convert to contiguous list

I have a made non-contiguous named range i.e. a group of ranges under one name
(I named the range from a filtered list and used "visiblecells" only)

When I try to use the name in formulas it does not like it one bit, because it is a non-contiguous range.

So I have to paste the range into a sheet and make a new name from the pasted list - this works but is slow and messy.

Is there a better way?

Any ideas?

Many thanks.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Thank you for the reply,

I have attached a simple file to demonstrate what I am trying to do.
the actual file is very complex.

The formula in cell G1 uses a named range that is generated from the list when the command button is pressed.
If the list is filtered, the formula has an error message,
when the list is not filtered, then the formula works.

Is there any way to change the name to a contiguous name to allow the formula to work?

Many thanks.

Why not just use

Formula:

Please Login or Register  to view this content.

Originally Posted by shg

You could use code to add a filter to col B and then count the visible cells.

EDIT: If the named range is not otherwise essential, Fluff's is a much better suggestion.

Thanks for the reply, however I need to use the named range. The example I gave was just to illustrate. But thanks fornthe good ideas :-)
I have been able to convert the non contiguous name into a contiguous name by pasting it into a sheet and then renaming it. This works well but takes 2 seconds. I was wondering if there was a sleeker way of converting the name.