Errors when clear variable Excel range using VBS

Question

I have a VBS script that copies an existing sheet and I need to clear a dynamic range of cells (depends on the particular excel workbook opened)

I am able to clear the range when it is hard coded:

Set xl                      = CreateObject("Excel.application")
fso.CopyFile strFileSelect, fso.GetParentFolderName(strFileSelect) & "\Newversion.xlsm"

wshShell.Run """" & fso.GetParentFolderName(strFileSelect) & "\Newversion.xlsm"""

xl.Application.Sheets("Auto").Range("E15:BP142").ClearContents

but get errors when I try

xl.Application.Sheets("Auto").Range(Cells(15, 5), Cells(142, 67)).ClearContents

Actually, I ultimately want to clear the range based on UBound of two arrays for the Range, something like:

xl.Application.Sheets("Auto").Range("E15", .Cells((UBound(ArrayText)+1), (((UBound(ArrayStepText) + 1) * 2) + 5))).ClearContents

I have tried also using cells instead of .cells, tried

set r = Range("E15", Cells(142,67))

and clearing r, that does not work either.

I also can't use xlUp or something like that as there are hidden and protected cells after my array range

There is a similar question

VBS for excel: Using a script variable in Range selection

but I cannot use the rebuilding of the string as I don't know the column letter that it ends with and would have to make a look-up table to determine the column letters from the UBound of the array and that number could go to 128.

Surely there is an easier/ more efficient way?

Thanks

You're using the global Cells and Range objects. Get a reference to the worksheet and use fully qualified references. — Comintern
– Comintern, Commented Oct 28, 2016 at 12:37

Tim Williams · Accepted Answer · 2016-10-28 17:20:59Z

0

Something like this should work (untested):

newPath = fso.GetParentFolderName(strFileSelect) & "\Newversion.xlsm"
fso.CopyFile strFileSelect, newPath

Set xl = CreateObject("Excel.application")
set wb = xl.Workbooks.open(newPath)
set sht = wb.Sheets("Auto")

set rng = sht.Range(sht.Range("E15"), _
          sht.Cells((UBound(ArrayText)+1), _
          (((UBound(ArrayStepText) + 1) * 2) + 5)))

rng.clearcontents

answered Oct 28, 2016 at 17:20

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BertB Over a year ago

I tried something similar, not exactly the same. I'm away from the office but will try it ASAP.

BertB Over a year ago

Thanks for that, the exact lines used are:

BertB · Accepted Answer · 2016-11-18 00:49:04Z

0

Thanks to comintern:

endRow    =    UBound(ArrayCriteria) + 14
endColumn =    UBound(ArrayText) + 4
xl.Range(xl.Application.Sheets("Auto").Cells(15,5),xl.Application.Sheets("Auto").Cells(endRow,endColumn).clearContents

Specify as an Excel range, then specify that the range starts in the excel application with full qualification

answered Nov 18, 2016 at 0:49

BertB

1181 silver badge9 bronze badges

Collectives™ on Stack Overflow

Errors when clear variable Excel range using VBS

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related