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?