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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
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
 

edTech

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

edTech

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

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

Watch MrExcel Video

Forum statistics

Threads
1,129,916
Messages
5,638,972
Members
417,063
Latest member
thematulaak

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
Top