Macro to select a named range depending on value

mwedikumenyanga

New Member
Joined
Jun 19, 2007
Messages
15
I need to create a macro which will select a named range depending on value in a named cell.
Cells A1:D10 contain data which can be 1 to 10 items.
Cells E1:E10 will change on each row from 0 to 1 when any of the cells in columns A1:D10 is not =0
Cell E11 contains total of E1:E10 and is named ItemTot
Cells A1:D10 is named Items10 and will be selected if ItemTot = 10
Cells A1:D1 is named Items1 and will be selected if ItemTot = 1
Cells A1:D2 is named items2 and will be selected if ItemTot = 2 etc

A solution to this problem will make my day.
Thank you
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
mwedikumenyanga

Welcome to the MrExcel board!

See if this is what you want:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SelectItems()
    Range("Items" & Range("ItemTot").Value).Select
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Peter,
Thank you very much. If only you know how much you've helped me. It worked like a magic.
No problem. :biggrin:

I am not sure what you are going to do with the range once it is selected, but be aware that generally you do not have to select a range to work with it in vba. In fact, selecting can slow down your code considerably.

For example, suppose you wanted to colour the range in question. It would be quicker (although not noticeable for this short example) to use:
Code:
Range("Items" & Range("ItemTot").Value).Interior.ColorIndex = 3
than to use:
Code:
Range("Items" & Range("ItemTot").Value).Select
Selection.Interior.ColorIndex = 3
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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