Filling blank cells based on neighbouring column

[ppataki]

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

[DonkeyOte]

Assuming they are as you say truly empty (and given the nature of the used range)

Sub Example()
On Error Resume Next
Columns(2).SpecialCells(xlCellTypeBlanks).Value = Range("D3").Value
End Sub

[ppataki]

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

[DonkeyOte]

I had a quick look at your code... if you're looking to copy the filtered range only perhaps:

Sub Button1_Click()
Dim rngPaste As Range
With ThisWorkbook.Sheets("Sheet1")
    Set rngPaste = .Cells(.Rows.Count,"A").End(xlUp).Offset(1)
End With
Workbooks.Open "C:\Users\u565873\Desktop\TEST source.xlsx"
With ActiveWorkbook.Sheets("Sheet1").Range("$A$7:$D$11")
    .AutoFilter Field:=3, Criteria1:="<>"
    .SpecialCells(xlCellTypeVisible).Offset(1).Copy rngPaste
    .AutoFilter
End With
ActiveWorkbook.Close False
Set rngPaste = Nothing
With ThisWorkbook.Sheets("Sheet1")
    .Range(.Cells(2, "A"), .Cells(.Rows.Count, "A").End(xlUp)).Offset(, 4).SpecialCells(xlCellTypeBlanks).Value = .Range("L1").Value
End With
End Sub

[ppataki]

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!

[DonkeyOte]

Whoops... misplaced Offset, change

.SpecialCells(xlCellTypeVisible).Offset(1).Copy rngPaste

to

.Offset(1).SpecialCells(xlCellTypeVisible).Copy rngPaste