one formula that does not require dragging the formula down unknown number of rows

Hello,

I have one formula the when i drag is down to row 100 the additional column use the double click + and auto fill.

=IF(A2=ISBLANK(TRUE)," ",A2)
is there a way i can enter the above formula in b2 that will autofill b2:b99999 with information only if cell A2:A9999 have data.

I wanted to stop dragging my formula down to an unknown row to account for new data. see my example. i currently drag the formula down to ROW 67. i only want my table to shift and change based on what is pulled not what could be pulled.

I do run a macro based off this so the less it has the reference the better. i don't know how to use visual basis but i am recording macros successfully but when i try to add or change in Visual Basic it does work so i know i am missing something

Change the data range to a table and the formulas will fill automatically as new data is added (ie. pressing tab in the last cell of the table inserts a new row)