Auto filling cells with no data using VBA

[forensic93]

Hi,

I was wondering if it was possible to back fill data using a macro?
I have one column where every 4th cell there is data, and one column where every 16th cell there is data.

Essentially i want to know if i can get a macro to read the 4th cell and if the 5th cell has nothing then write what the 4th cell has in it. But if there is data, then that becomes the new number used to fill in empty cells if that makes sense.

Is this possible to do? I have added my attempted code below.

Sub fill_cell() 
    Dim rw As Long 
    Dim Sheet As interger
    Sheet = 1
    With Sheets("Sheet") 
for rw = 1 To Cells(Rows.Count, "B").End(xlUp).Row 
            If Cells(rw, "E") = "" Then Cells(rw, "E") = Cells(rw, "E4")
        Next rw 
    End With 
End Sub

 

[gtwo]

Check the image below

I hope this works

Sub fill_cell()
    Dim rw As Integer
    
    With Sheets("Sheet")
        For rw = 1 To .Range("A500000").End(xlUp).Row
            If .Range("A" & rw).Value = "" Then
               .Range("A" & rw).Value = .Range("A" & rw - 1).Value
            End If
        Next rw
    End With
End Sub

 

[forensic93]

For rw = 1 To .Range("A500000").End(xlUp).Row

I get a runtime error at this point

 

[Michael M]

Try

Sub fill_cell()
    Dim rw As Long
    With Sheets("Sheet")
    For rw = 1 To Cells(Rows.Count, "B").End(xlUp).Row
    If .Range("A" & rw).Value = "" Then
               .Range("A" & rw).Value = .Range("A" & rw - 1).Value
            End If
        Next rw
    End With
End Sub

 

[forensic93]

Try

Thank you Michael! Works a charm!

 

[Peter_SSs]

Don't know how big your actual data is, but you can do them all at once instead of cycling through a row at a time.

Sub fillblanks()
  With Range("A1", Range("B" & Rows.Count).End(xlUp).Offset(, -1))
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub