select cells

[buzz71023]

Is there a way to select a range between two cells with a defined name? For example, i have a two named cells (notes1 and notes2) that are not always in the same place (columns are added between the two, but between those ranges i need to select everything (not including the two named ranges).

 

[buzz71023]

Sheets("Data Sheet").Select
Range(Range("Notes1").Offset(0), Range("Notes").Offset(-1)).Select

this is what i have but it is selecting the named cells

 

[jasonb75]

Try

Sheets("Data Sheet").Range(Range("Notes1").Offset(,1), Range("Notes").Offset(,-1)).Select

Do you really need to select? It's inefficient, most actions can be carried out directly without selection.

 

[buzz71023]

for some reason it is selecting the entire named range of Notes, the other part is working correctly, it did offset to the right of notes1.

 

[jasonb75]

Are there any merged cells that span Notes and the column to the left?

 

[buzz71023]

No, the only merged cells are the ranges of notes1 (c2,g3 and will always be that range) and notes (changes with every column added, but for the sake of the example ill say it is m2,g3).

 

[Rick Rothstein]

No, the only merged cells are the ranges of notes1 (c2,g3 and will always be that range) and notes (changes with every column added, but for the sake of the example ill say it is m2,g3).

It looks like there may be a typo in the formula...

Sheets("Data Sheet").Range(Range("Notes1").Offset[COLOR=black](,1[/COLOR]), Range("Notes[SIZE=3][COLOR=red][B]2[/B][/COLOR][/SIZE]").Offset([COLOR=black],[/COLOR]-1)).Select

Add the 2 shown in red and I think the formula will work for you.

 

[buzz71023]

it didnt select both the rows, just the top row but it did select between the two rows this time and that will work if need be, but I was just hoping for both rows to be selected. its just odd that even worked at all bc i dont have a range named Notes2. But thank you for your help so far.

 

[jasonb75]

How do you have the named ranges defined? Is it done by formula or direct reference to the cells?

When you say c2,g3 do you mean it only refers to 2 cells, or it refers to c2:g3?

 

[Rick Rothstein]

i dont have a range named Notes2.

???

Your first message said "For example, i have a two named cells (notes1 and notes2)"... so why don't you have a range named Notes2 then???