Dynamic Range for macro

Hi,

I've a coding issue I've been unable to solve so far and google didn't help this time..

I'd need to define a range starting at the last used cell of column Q and finishing at the last row used in column A (Columns A to I are getting datas from an external source)

I had no problem to find how to start at last used cell, but I can't find the trick to define where the range has to stop.

I actually uses a named range, based on column A length, to run a query but it starts therefore at the beginning of the columns and uses times/ressources for "nothing"..



Here's the named range I actually use in my macro :

=Sheet1!$Q$2:INDEX(Sheet1!$Q:$Q;COUNTA(Sheet1!$A:$A))

Any idea how I could solve this ?
Thanks in advance for your support,

regards
steve

Post deleted as I'm unable to read questions properly today

Dom

=offset(a1;counta(q:q);0;counta(a:a)-counta(q:q);16)

This was the answer I originally posted which turns out was correct:

Please Login or Register  to view this content.

It's best not to hard code row numbers like 65000 into your code as the number of rows in different versions of Excel now vary.

Dom

The solution is even simpler:

=offset(Q1,counta(q:q),0,counta(a:a)-counta(q:q),0)