I have named ranges with these values in different parts of the workbook:
Named_range_1:
1
2
3
Named_range_2:
4
5
6
Named_range_3:
7
8
9
Im trying to get all of these named ranges into one array like this:
array_1:
1
2
3
4
5
6
7
8
9
I tried to write the code but it will only accept one named range at a time:
Please Login or Register to view this content.
How can I load more named ranges into the array?
Thankful for any help!
Last edited by Lollpopp; 01-01-2023 at 09:31 AM.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however you need to include code tags around your code.
Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.
Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Please Login or Register to view this content.
Good Luck
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the star to left of post [Add Reputation]
Also....add a comment if you like!!!!
And remember...Mark Thread as Solved.
Excel Forum Rocks!!!
This code only picks up the first named range and skips the rest when i try it.
It seems it doesnt allow to put named ranges like this in one line of code.
Where is your sample file...Is it non contiguous named ranges?
I assumed continuous...
Missed this...
in different parts of the workbookPlease Login or Register to view this content.
Last edited by sintek; 01-01-2023 at 12:09 PM.
Late submission if all else fails.
Torachan,
Mission statement; Promote the use of Tables, Outlaw the use of 'merged cells' and 'RowSource'.
... continue ...
If we know and are sure that the named ranges are "one-dimensional":
, where: "nr_1" = "Named_Range_1", and so onPlease Login or Register to view this content.
Maybe, without VBA, try:Formula:
Please Login or Register to view this content.
Last edited by TMS; 01-03-2023 at 12:53 AM. Reason: Add bracket
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I tried using VSTACK. To be able to adapt the named ranges to different circumstances was good
in my case.
I created the following setup for this:
A table of named ranges that can be individually activated using toggle 1/0.
The named ranges gets combined using the textjoin command in cell A18.
This cell is named "x_list_named_ranges"
The macro CombineNamedRanges is used to put an updated formula in cell A25:
Please Login or Register to view this content.
Perhaps its enough to put the resulting range into an array.
I dont know if this runs slowly if the the named ranges are
filled with lots of rows.
To make everything above in VBA only is beyond me right now.
I uploaded the workbook!
Combine named ranges.xlsm
What is it you are you actually wanting to achieve and why is your data in named ranges?
Explain your required process in step by step...
Are you wanting to SELECT FROM list your named ranges and then combine those named ranges into 1 column?
See if this is what you are after...make your 1 or 0 selections then press button...
Please Login or Register to view this content.
Last edited by sintek; 01-03-2023 at 10:27 AM.
I have the data in named ranges because I can easily reference them in code or formulas.
Isnt this a good way to reference ranges in code?
I want to read from a range in the worksheet, just like your example.
The data im reading will be used to draw geometry in Autocad.
So the data contains all the different characters: numbers, digits, semicolons, commas.
I've updated your file with some sourcedata.
I had problems with transpose when i tried with arrays. When creating the array
eveything is fine. If i look in the locals window all the data is correct.
For example: value in Named_range_1 marked with yellow says "2930,540" in the worksheet and in the locals window.
But when i write array to workbook then comma character disappears for some reason, see yellow cell in range A27.
How come characters can be deleted like this?
locals-window.jpg
Combine named ranges - 2.xlsm
Has nothing to do with the characters being deleted...Has to do with the formatting of the cell...Why are you wanting to first store into array and then to sheet...Why not just from range to sheet directly?
Last edited by sintek; 01-03-2023 at 12:02 PM.
Perhaps this would be more suitable...
Please Login or Register to view this content.
I was thinking arays would be faster if i have many many rows in the named ranges
and i have to loop through many different sets of the named ranges.
In the end the array will be read into a .txt file. I read to worksheet to test!
What do you mean with formatting of the cell? One character has been deleted!
Click on the two yellow cells individually and see their number formats...1 is number and 1 is generalWhat do you mean with formatting of the cell? One character has been deleted!
Anyway post 13 solves ... no need to loop each individual cell to store into array...
Hello Lollpopp.
If you allow me, I'll give you some good advice:
- If you're going to use VBA... Think like VBA!
- And if you are going to use Excel... Think like Excel!
When you "mix" such reasoning unnecessarily, you end up with long, inefficient VBA code and questions on the Forums that are not fully understood.
Then:
Note that in the attached workbook I have not left a single range with a name... Because there was no need!
Please Login or Register to view this content.
Last edited by beyond Excel; 01-03-2023 at 03:33 PM.
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
Another simplistic solution...Making use of beyond Excel file
And if you still cannot make use of any of the above options then I suggest you go back to the drawing board...Please Login or Register to view this content.
Last edited by sintek; 01-03-2023 at 04:09 PM.
Thank you all especially sintek for your help in this matter! Your help is much appreciated!
So...Feedback...Which solution have you gone with...Is your original query answered...If so please mark thread as solved...
And don't forget to add reputation (click on *) to the messages that have been useful to you.Originally Posted by Lollpopp
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks