filling in blanks in excel using VbScript

[edTech]

Good afternoon,
I need some help with my VbScript as I am trying to fill in blanks in my excel sheet. My current code is breaking in Selection portion as I do not know how to make it work in Vbscript. Can you please help?

---define objects----
Set objReadXL = CreateObject("Excel.Application")
Set fso = CreateObject("Scripting.FileSystemObject")

'----define constance----
Const xlup = -4162
Const xlDown = -4121
Const xlToRight = -4161
Const xlToLeft = -4159
Const xlNone = -4142
Const xlCellTypeBlanks = 4


'Set variable definitions
'Make excel spreadsheet visible
objReadXL.Visible = true

Set objReadWB = objReadXL.Workbooks.Open("testinggap.xlsx")
Set objReadWS = objReadWB.Sheets("Sheet1")
'Set objchart = objReadXL.Charts.Add()


With objReadWS

'Get the last row and last column
lastRow = .Range("A" & .Rows.Count).End(xlUp).Row
lastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

'Set the range
Set rng = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))
rng.Select

'Select Blanks
rng.SpecialCells(xlCellTypeBlanks).Select

'Fill Blanks with value above
Selection.FormulaR1C1 = "=R[-1]C"

'Paste Formulas as Values
rng.Select
Selection.Copy
Selection.PasteSpecial -4163, -4142, True, False

End with

 

[Norie]

Try this.

Set rng = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))

With rng
    'Select Blanks and Fill Blanks with value above
    rng.SpecialCells(xlCellTypeBlanks)[B].FormulaR1C1 = "=R[-1]C"[/B]

    'Paste Formulas as Value
    .Copy
    .PasteSpecial -4163, -4142, True, False
End With

 

[edTech]

Try this.

Set rng = .Range(.Cells(1, 1), .Cells(lastRow, lastCol))

With rng
    'Select Blanks and Fill Blanks with value above
    [B][COLOR=rgb(184, 49, 47)]rng.SpecialCells(xlCellTypeBlanks)[B].FormulaR1C1 = "=R[-1]C"[/B][/COLOR][/B]

    'Paste Formulas as Value
    .Copy
    .PasteSpecial -4163, -4142, True, False
End With

Thank You Norie, it is throwing an error on the I highlighted line in Red. Error: Expected end of statement

 

[edTech]

Thank You Norie, it is throwing an error on the I highlighted line in Red. Error: Expected end of statement

I fixed the end of line issue, but I get an Invalid or unqualified reference on this line: lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

 

[edTech]

I fixed the end of line issue, but I get an Invalid or unqualified reference on this line: lastRow = .Range("A" & .Rows.Count).End(xlUp).Row

Thank you Norie, I figured out the reference issue, and it is working like a charm .... Thank you very much