Jul 05 2021 05:34 PM
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, , , , , 8)
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.
Jul 05 2021 09:22 PM
Solution
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
Jul 05 2021 10:09 PM
Jul 05 2021 10:27 PM
You're welcome @Jadepad! Glad it worked as desired.
Jul 05 2021 09:22 PM
Solution
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