set range object to named range with non contiguous cells

OaklandJim

Well-known Member
Joined
Nov 29, 2018
Messages
863
Office Version
  1. 365
Platform
  1. Windows
I am trying to use a named range in a workbook that has non contiguous cells. When I try to set a range object to a named range with contiguous cells the command works. If I try to set the range object to a named range with non contiguous cells Excel chokes. I get an error # 1004 Application-defined or object-defined error. Googling results seem to indicate that I should be able to use VBA with VBA range objects that have non contiguous cells. Is this doable? What should I try?

I have a table with 10 data rows and three "section rows" and I need to process just the 10 data rows that are not contiguous.
 
I am trying to use a named range in a workbook that has non contiguous cells. When I try to set a range object to a named range with contiguous cells the command works. If I try to set the range object to a named range with non contiguous cells Excel chokes. I get an error # 1004 Application-defined or object-defined error. Googling results seem to indicate that I should be able to use VBA with VBA range objects that have non contiguous cells. Is this doable? What should I try?

I have a table with 10 data rows and three "section rows" and I need to process just the 10 data rows that are not contiguous.
What is in the Refers To box for the named range?

I've tried it and it works fine but we may be doing something different.
 
Upvote 0

HighAndWilder

As often happens I should have known that I was doing something incorrectly and Excel was behaving as advertised. OF COURSE I can define range names with non contiguous cells.

My name definition was screwed up so Excel kept choking on it. I relooked at the RefersTo for the name several times -- pusuant to your question -- and I had not noticed that it was trying to use a name pointing to the wrong worksheet. So the correct worksheet did not even have the range name that I was trying to use.

Lame-assed trubble shooting eh? Thanks for the assist. I apologize for wasting your time.

Low and Tamer (Jim)
 
Upvote 0
Solution

Forum statistics

Threads
1,226,771
Messages
6,192,918
Members
453,766
Latest member
Gskier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top