how to define range for autofill without knowing the last row .
 

In Excel Macro i am using, i need to define the range for
Autofill(eg.B1:B5) but the last row defined here B5 can vary with new
worksheets. I need to use a formulea to calculate the last row available
with data such that it can be used (eg B1: B(=counta(a1:a100)).where counta
provides the number of last row to be filled..?? The counta that i
tried to use aove does'nt work... Can you HELP..!