filling in blanks in excel using VbScript

edTech

New Member
Joined
Dec 8, 2019
Messages
33
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Try this.
VBA Code:
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
 
Upvote 0
Try this.
VBA Code:
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top