Filling blank cells based on neighbouring column

Dear All,

I have a sheet that contains 2 colunms, A and B; they look like below:

A: B:
X 1
Y (empty)
Z (empty)

The last two cells in column B are empty, but this is subjected to change
I would like to paste the value of a cell (eg D3) to fill in the empty cells in column B with VBA (the last n cells are empty, there are no other empty cells)

Could you please help?
Many thanks

Hi!

This works, but the problem is that it fills in almost 40 cells in column E while I would need it to fill only 2 cells
I am attaching the file, so you can see what I am talking about
(the value in L1 should be pasted into the yellow cells, and it also may happen that there are more cells to be filled in ,depending on the number of lines already present in columns A:D)

Many thanks in advance

This looks great!!! Thank you!!
The only problem is that because of offset with the autofilter it pastes 1 row less, but if I delete offset it pastes the header row as well....any tips?

Many thanks again!