Filling out blank spaces with previous value

[edTech]

I have data in an excel sheet that has blank spaces in column B and C. I want to fill them with its previous values. I have the VbScript code below semi working as I think my issue is the FormulaR1C1, but I cannot get it to work. Any suggestions?

Sample Data:

A​	B​	C​
Time	Start Race	End Race
2255	86.5	86.5
2634		98.5
5789	88.9

Code:

With objReadWS

lastRow = .Range("B" & .Rows.Count).End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

Set rng = .Range(.Cells(2, 2), .Cells(lastRow, lastCol))
rng.Select

rng.SpecialCells(xlCellTypeBlanks).Select

rng.FormulaR1C1 = "=R[-1]C[2]"

rng.Select
rng.Copy
rng.PasteSpecial -4163, -4142, True, False


End With

 

[Fluff]

How about

   With objReadWS
      lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
      lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
      
     With .Range(.Cells(2, 2), .Cells(lastrow, lastCol))
         .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
         .Value = .Value
      End With
   End With

 

[JLGWhiz]

Or without formulas

Sub t()
Dim c As Range
For Each c In Range("B2", Cells(Rows.Count, 1).End(xlUp).Offset(, 2))
    If c = "" Then c = c.Offset(-1).Value
Next
End Sub

 

[edTech]

Thank you Fluff and JLGWhiz both methods worked fantastic.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[edTech]

Glad we could help & thanks for the feedback

Your welcome, if I wanted to stop at column C instead of having it go to the last empty column how would I change this line? lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

 

[Fluff]

Do you mean you only want it to work on columns B & C?

 

[edTech]

Do you mean you only want it to work on columns B & C?

Yes, please

 

[Fluff]

How about

   With objReadWS
      lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
      lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
      
     With .Range("B2:C" & lastrow)
         .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
         .Value = .Value
      End With
   End With

 

[edTech]

How about

   With objReadWS
      lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
      lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
     
     With .Range("B2:C" & lastrow)
         .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=r[-1]c"
         .Value = .Value
      End With
   End With

Perfect! that was awesome ... I was struggling all night with that