Using function result as cell address

Hi,

I would to use the result of a function as the address of a cell. For example here:
Formula1:="=Sheet2!$A$2:$A$300"

I want to replace 300 with the result of a function inside a VBA or the data validation range. Either something like

$A$2:$A$(COUNTA(A:A)), where counta returns a number (50, 55 or whatever) OR
$A$2: (ADDRESS(COUNTA(A:A),1)), where Address returns something like $A$50.

Any ideas?

Hi again and thanks!

I want to make a unique data validation list and I do that by using a macro. In the Macro:

-First I copy the column (rows + 200/300 more for future inputs) to another (hidden/useless) sheet
-I remove the duplicates
-Then I use a data validation list by using the unique list but I want a dynamic range so as NOT to
have many blanks from the extra blank rows.

So in the macro I want to use has a range like:
Formula1:="=Sheet2!$A$2:$A$(COUNTA(A:A))"

So instead of having a fixed end, define the end of the range as the last non blank cell of column A.
COUNT(A:A) gives that, e.g. 50 or 60, so I want to use the end (A60 in this case) through this function.

Please Login or Register  to view this content.

I believe

I don't think it works...

Can you be more specific - it seems to be ok to me, how doesn't it work?

Note, I think the most common method for getting the last non blank cell is:
Range("A" & rows.count).end(xlup)

So for your formula this would be:
Formula1:="=Sheet2!$A$2:$A$" & Range("A" & rows.count).end(xlup).row

I think it works now, thanks! You just need your sheet also in the range.

Formula1:="=Sheet2!$A$2:$A$" & Range("Sheet2!A" & rows.count).end(xlup).row