SOLVED

Please help: Fill Blank Cells Down with VBA Marco

New Contributor

Jadepad_1-1625531094227.png

Hi there, I want to Fill Blank Cells Down with VBA Marco as:

Sub FillBlankCellsDown()
Dim xRg As Range
Dim xCell As Range
Dim xAddress As String
On Error Resume Next
xAddress = Application.ActiveWindow.RangeSelection.Address
Set xRg = Application.InputBox("Select a range:", "Excel", xAddress, , , , ,
If xRg Is Nothing Then Exit Sub
For Each xCell In xRg
Range.SpecialCells(xlCellTypeBlanks).Select
Range.FormulaR1C1 = "=R[-1]C"
Next
End Sub

 

But the Range not working.

 

 

 

3 Replies
best response confirmed by Jadepad (New Contributor)
Solution

@Jadepad 

 

Please try this and see if that works for you.

 

Sub FillBlankCellsDown()
Dim Rng As Range
Dim xRg As Range

On Error Resume Next
On Error Resume Next
Set xRg = Application.InputBox("Select a range:", "Excel", Type:=8)

If xRg Is Nothing Then Exit Sub

Set Rng = xRg.SpecialCells(xlCellTypeBlanks)

On Error GoTo 0

If Rng Is Nothing Then Exit Sub

Rng.FormulaR1C1 = "=R[-1]C"
xRg.Value = xRg.Value

End Sub
Yes, your macro works. Awesome. Thanks very much.

You're welcome @Jadepad! Glad it worked as desired.