dinamic dependent range

[drom]

Hi and Thanks in advance!

I have a excel database eg: range("A1:M50") on the sheet named: Country
the first row of this sheet shows eg: a1=FRANCE, b1=ITaly, c1=Spain and son on
from 2nd row down I show the cities of this countryes.
so eg: A2=Biarritz, A3=Bordeaux, A4=Lyon A5=Marseille, A6=Paris, etc

In one other sheet I have a cell named "rCOuntry", which contains a data validation and shows the first row of the sheet: "Country" so Sheets("Country").range("A1:M1")


Now I would like to create one Dinamic range named "rDB", so when the range("rCOuntry") shows Italy, the dinamic range will get the data from B1 to Bxx being (xx=Dinamic)

I could do 13 dinamic ranges kind of:
rFrance=OFFSET($A$1,1,0,COUNT($A:$A)-1,1)
rItaly=OFFSET($B$1,1,0,COUNT($B:$B)-1,1)

but for sure there is a easyest way


Any IDEA?

 

[DEMK]

Hi Name is Darryl new of website
Looking through the other QA today noticed a method of assigning a dynamic ranges you might like to use. It sets a range using key stroke rather then the offset method using the B Column as an example

Dim Towns As Range
Towns = Cells(Rows.Count, "B").End(xlUp).Row

Guess you could replace the absolute Column reference "b" with ActiveCell.Column making sure you code activates the Column in question beforehand

 

[drom]

OK, this does the job OK:


=OFFSET($A$1;1;MATCH(rCOuntry;rDptosAll;0)-1;COUNTA(OFFSET($A$1;1;MATCH(rCOuntry;rDptosAll;0)-1;1000;1));1)

being:
rDptosAll="A1;M1"