I want to fill every blank cell in my range with data from the cell above, including one row below the last row. I thought that I could achieve this with range offset but I get "Object required" error.
Any advice?
VBA Code:
Sub fillBlanks()
Dim lrow As Long
Dim ws As Worksheet
Dim rng As Range
Set ws = ThisWorkbook.Worksheets("Journal")
lrow = ws.Range("A" & Rows.Count).End(xlUp).Row
Set rng = ws.Range("A2:A" & lrow)
With rng.Offset(1, 0)
On Error Resume Next
Set myRange = .SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not myRange Is Nothing Then
myRange.FormulaR1C1 = "=R[-1]C"
.Value = .Value
End If
End With
End Sub
Any advice?