Excel Office Script Examples

Content

Introduction
Video
Entering zeroes in empty cells
Last Used Row
Adding a table of Contents to your workbook
Adding a list of Range names
Prompting for user input
Filter a table by date
Download

Introduction

Since July 2020, Microsoft 365 users can record their actions in Excel on-line into Office Script macros. Earlier, I've posted an article that shows you how to get started. On this page, I will list some example scripts which you cannot record but might come in handy.

Some of these scripts contain literal values, (the values between quotes). This means things like worksheet names are hard-coded into the example scripts: "ToC". So if you need a different worksheet name, edit the script accordingly. Because Microsoft has not yet given us a way to prompt the user for a value, we must edit the script to change what sheet it affects.

Download the full code (zip file with all scripts) here.

Video

I've presented parts of this page during Excel Virtually Global 2022. Here's the Youtube recording:

Entering zeroes in empty cells

Suppose you want to enter a zero in each empty cell in the selection:

Range of cells with empty cells

This code does exactly that.

function main(workbook: ExcelScript.Workbook) {

// Your code here

let emptyAreas = workbook.getSelectedRange().getSpecialCells(ExcelScript.SpecialCellType.blanks);

emptyAreas.getAreas().forEach(r => {

r.setValue(0);

});

}

Last Used Row

The function getLastRow shown below, returns the last used row of any range (assuming the cell on row 1048576 is empty!)

function main(workbook: ExcelScript.Workbook) {

const sht = workbook.getWorksheet("Sheet2");

const lastRow = getLastRow(sht, sht.getRange("H:H"));

console.log(lastRow);

sht.getRange("H2:H" + lastRow).select();

}

function getLastRow(sht: ExcelScript.Worksheet, rng: ExcelScript.Range) {

let lastCell = sht.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());

if (lastCell.getValue().toString().length > 0) {

return "Last row on sheet filled"

} else {

return lastCell.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;

}

Notice that the script will return a string instead of a number if the last cell of the sheet contains something. This will cause an error in the calling routine which you must handle.

Adding a table of Contents to your workbook

Working in Excel Online and need a table of Contents for your workbook quickly? Here's an Office Script that produces one. It generates a list for all worksheets and all charts, with hyperlinks pointing to their locations.

First let me show the main function and explain it step by step.

function main(workbook: ExcelScript.Workbook) {

if (workbook.getWorksheet("ToC")) {

let temp = workbook.getWorksheet("ToC")

temp.delete();

}

let allSheets = workbook.getWorksheets();

let tocSheet = workbook.addWorksheet("ToC");

let reportCell = tocSheet.getRange("B2");

let newTable = addTocTable("Worksheets", workbook, reportCell);

reportCell = reportCell.getOffsetRange(2, 0);

reportCell.getResizedRange(0, 1).setValues([["Worksheet", "Link"]]);

for (let i = 0; i < allSheets.length; i++) {

reportCell.getOffsetRange(i + 1, 0).setValue(allSheets[i].getName());

}

reportCell.getOffsetRange(1, 1).setFormulaR1C1("=HYPERLINK(\"#'\"&RC[-1]&\"'!A1\",RC[-1])");

reportCell = reportCell.getOffsetRange(newTable.getRowCount() + 2, 0)

newTable = addTocTable("Charts", workbook, reportCell);

reportCell = reportCell.getOffsetRange(2, 0)

reportCell.getResizedRange(0,3).setValues([["Worksheet", "Name", "Location", "Link"]]);

let k = 0;

for (let i = 0; i < allSheets.length; i++) {

let allCharts = allSheets[i].getCharts();

for (let j = 0; j < allCharts.length; j++) {

k++;

let theSheet = allSheets[i];

reportCell.getOffsetRange(k, 0).setValue(allSheets[i].getName());

reportCell.getOffsetRange(k, 1).setValue(allCharts[j].getName());

let topLeftCell = getCellUnderChart(allCharts[j], allSheets[i]);

let topLeftAddress = topLeftCell.getAddress();

if( topLeftAddress.charAt(0)=="'"){

topLeftAddress="'" + topLeftAddress;

}

reportCell.getOffsetRange(k, 2).setFormula(topLeftAddress);

}

reportCell.getOffsetRange(1, 3).setFormulaR1C1("=HYPERLINK(\"#\"&RC[-1],\"Link\")");

tocSheet.activate();

}

These are the important steps in this code:

check whether a worksheet called ToC exists and if it does, delete it.
Fetch a list of all worksheets and store that in a variable called allSheets
Add a worksheet called "ToC" and assign it to a variable called tocSheet
Assign cell B2 to the variable reportCell
Add a table to the table of contents using the function addTocTable (see below)
To ensure we start adding the sheet names on the first empty row of the table, we offset the reportCell with two so it now points to cell B4
We add the headings (Worksheet and Link) to the newly inserted table
Now it is time to loop through the allSheets collection and list the name of each sheet in the first column of the table
After looping through the sheets, we add the HYPERLINK formula to the second column, using the name of the worksheet in the first column to create a link to each worksheet
We offset the reportCell position by adding the number of rows in the worksheet table (plus two rows extra for spacing)
Now we add the heading for the charts table and an empty table to hold them, using that addTocTable function again
The Charts table has four columns: Worksheet, Name, Location and Link
We run through all worksheets and for every worksheet we
- run through all charts
- fill the chart table with the appropriate information: Worksheet, Name and Location (location required me to design a function called getCellUnderChart because Office Script does not have a TopLeftCell property of a chart like VBA has)
- There's a snag for charts on sheets which contain a space in their name; the correct address of the topleftCell in sheets like that have apostrophes around the name: 'Blad 2'!F1. However, if you write 'Blad 2'!F1 to a cell, Excel strips that first apostrophe. Hence the if statement that adds an additional apostrophe in such cases.
Once we've done all charts on all worksheets, we place the HYPERLINK formula in the last column again using the address information in the third column

The main function uses two helper functions, here's the addTocTable one:

					function addTocTable(caption: String, workbook: ExcelScript.Workbook, location: ExcelScript.Range): ExcelScript.Table {

					//1. adds a caption in reportCell and formats that as Heading 1

					//2. Adds a table below reportCell to hold ToC entries

					//Returns: the table

					  location.setValue(caption);

					  location.getResizedRange(0, 1).setPredefinedCellStyle(ExcelScript.BuiltInStyle.heading1);

					  location = location.getOffsetRange(2, 0);

					  let newTable = workbook.addTable(location, true);

					  return newTable;

					}

All it does is add the caption in the location cell, style it as "Heading 1" and add a new one-column table. The function returns the table as an object so we can use that object in our calling routine.

Note that you can also address a style by its name. But be aware that built-in style names are language-dependent. So only assign a style by its name if it is a custom style.

And here is the getCellUnderChart function:

function getCellUnderChart(cht: ExcelScript.Chart, ws: ExcelScript.Worksheet): ExcelScript.Range {

let topLeftCell = ws.getRange("A1");

do {

topLeftCell = topLeftCell.getOffsetRange(0, 1)

}

while (topLeftCell.getLeft() < cht.getLeft());

do {

topLeftCell = topLeftCell.getOffsetRange(1, 0)

}

while (topLeftCell.getTop() < cht.getTop());

return topLeftCell.getOffsetRange(-1, -1);

}

The basic principle is simple: the function first loops through the cells of the worksheet horizontally, starting from cell A1, until it has found a cell with a Left value that is greater than or equal to the chart's Left position. After that it does the same vertically to find the Top position. Because the two loops take us one column too far to the right and one row too far down, we offset the found cell by (-1, -1) so we return the correct cell which denotes where the top-left corner of the chart is located.

Adding a list of Range names

Desktop Excel has a simple feature to add a list of range names to your workbook (Formulas tab, Use in Formula drop-down, Paste List). But Excel on-line does not (yet) have that feature. Here is an Office script which adds a worksheet called "Names List" to your workbook, listing all range names and some of their properties.

function main(workbook: ExcelScript.Workbook) {

var RowCt = 1;

var sheetName = "";

let MyNames = workbook.getNames();

let selectedSheet = workbook.getWorksheet("Names list");

if (selectedSheet) {

selectedSheet.delete();

}

let worksheets = workbook.getWorksheets();

selectedSheet = workbook.addWorksheet("Names list");

selectedSheet.getRange("B2:E2").setValues([["Scope", "Name", "RefersTo", "Visible"]]);

for (let i = 0; i < MyNames.length; i++) {

RowCt++

selectedSheet.getCell(RowCt, 1).getResizedRange(0, 3).setValues([

["Workbook", MyNames[i].getName(), "'" + MyNames[i].getFormula(), "'" + MyNames[i].getVisible()]]);

}

for (let i = 0; i < worksheets.length; i++) {

sheetName = worksheets[i].getName();

var myNames = worksheets[i].getNames();

for (let j = 0; j < myNames.length; j++) {

RowCt++;

selectedSheet.getCell(RowCt, 1).getResizedRange(0, 3).setValues([

[sheetName, myNames[j].getName(), "'" + myNames[j].getFormula(), "'" + myNames[j].getVisible()]

]);

}

let newTable = workbook.addTable(selectedSheet.getRange("B2").getSurroundingRegion(), true);

newTable.getRange().getFormat().autofitColumns();

}

The resulting table looks like this:
List of range names

Prompting for user input

OfficeScript does not have a feature like the VBA InputBox, nor can you design input screens.

Recently, Microsoft has provided a way to solve that issue. If you add input parameters to your function definition, Excel will show a little prompt window asking for their values. It will even add space characters before each capitalized letter of your variable name. Take this script for example:

					function main(workbook: ExcelScript.Workbook, StartDate:string, EndDate:string) {

					    console.log(StartDate);

					    console.log(EndDate);

					}

If you execute this script, this is what is shown:

A prompt for input

There is some intelligence to this. If a parameter is a boolean, it will present a drop-down with true/false. And you can set default values too. In addition, I wanted to define the StartDate and EndDate parameters as "Date", but apparently that isn't a valid data type for TypeScript. I was hoping it'd recognize this and offer me a date picker. O well, at least we can prompt the user for input values.

Alternative to using arguments

If you need more elaborate input, perhaps this offers some help.

The script below checks for presence of a worksheet called "Script_Input". If that sheet is not in the workbook, it gets added. Next a function is called which allows you to pass it a prompt and an array of inputs, which will be added to the Script_Input sheet. The resulting input sheet looks like this:

An input sheet for Office Script

If the script detects that the "Script_Input" sheet is available, it assumes all input has been completed and runs an action with that input (writes to the console).

This is the entire script:

function main(workbook: ExcelScript.Workbook) {

if (workbook.getWorksheet("Script_Input")) {

//we assume input is there, here are your script's main actions

let inputSheet = getInputSheet(workbook, "Script_Input");

let name = inputSheet.getRange("B3").getValue();

let dob = inputSheet.getRange("B4").getValue();

console.log(name + " " + dob);

}

else {

createPromptSheet(workbook, "Please enter your details below", ["Name", "DoB"])

//It may be a good idea to delete the Script_Input sheet now

}

function createPromptSheet(workbook: ExcelScript.Workbook, prompt: string, questions: string[]) {

let inputSheet = getInputSheet(workbook, "Script_Input");

let titleCell = inputSheet.getRange("A1")

titleCell.setValue(prompt);

titleCell.setPredefinedCellStyle("Heading 1");

titleCell.getFormat().autofitColumns();

let questionCell = inputSheet.getRange("A3");

let rw = 0;

for (let i = 0; i < questions.length; i++) {

rw++;

questionCell.getOffsetRange(i, 0).setValue(questions[i]);

questionCell.getOffsetRange(i, 0).setPredefinedCellStyle("Heading 4");

questionCell.getOffsetRange(i, 1).setPredefinedCellStyle("Input");

}

inputSheet.activate();

questionCell.getOffsetRange(rw + 2, 0).setValue("After entering all data, click the script button again.");

questionCell.getOffsetRange(rw + 2, 0).setPredefinedCellStyle("Heading 4");

questionCell.getOffsetRange(0, 1).select();

}

function getInputSheet(workbook: ExcelScript.Workbook, name: string) {

if (workbook.getWorksheet(name)) {

return workbook.getWorksheet(name);

}

else {

return workbook.addWorksheet(name);

}

Filter a table by date

Suppose you have a table with a date column and you want to filter that table by dates prior to today.

A date column in a table

To make this happen, you need the proper date format which applies to your Excel settings; is it mm/dd/yyyy or dd-mm-jjjj? For this you use the so-called cultureInfo of the Excel application:

workbook.getApplication().getCultureInfo().getName()

This returns a string in the shape en-US or (in my case) nl-NL. This string can then be used to convert the current system date to the proper locale string.

To filter this table for dates prior to today, you use code like this:

function main(workbook: ExcelScript.Workbook) {

let table1 = workbook.getTable("Table1");

table1.getAutoFilter().clearCriteria();

//Get date in local format

const date = new Date().toLocaleDateString(workbook.getApplication().getCultureInfo().getName());

//Apply filter to 'Table1' column 'date'

table1.getColumnByName("date").getFilter().applyCustomFilter("<" + date);

}

You can also use the ISO date format, which should work too:

function main(workbook: ExcelScript.Workbook) {

let table1 = workbook.getTable("Table1");

table1.getAutoFilter().clearCriteria();

//Get date in ISO format

const date = new Date().toISOString().slice(0, 10);

//Apply filter to 'Table1' column 'date'

table1.getColumnByName("date").getFilter().applyCustomFilter("<" + date);

}

Download

Download the full code (zip file with all scripts) here.

Next step: Call this script from a Power Automate Flow

In my next article I describe how to apply the Table Of Content script to all Excel files in a Sharepoint folder using Power Automate

Comments

All comments about this page:

Comment by: John from Ireland (24-8-2021 19:21:00) deeplink to this comment

This is an extremely useful script which I will use for my daily reporting needs.

Comment by: Jan Karel Pieterse (25-8-2021 11:00:00) deeplink to this comment

Hi John,

Thanks. Please note that I've updated the script today so it works correctly for both sheets with and without a space in the sheet name

Comment by: MIKE SMITH (27-8-2021 21:12:00) deeplink to this comment

Thanks for this. I have so many questions...
Does this code editor expand?
How do you debug?
Is there an equivalent of VBA's Immediate Window?

Comment by: Jan Karel Pieterse (27-8-2021 23:45:00) deeplink to this comment

Hi Mike,

We're limited to a maximum width of the editor.
Debugging is a struggle, there is no way to step the code for example so you have te revert to inserting console.log statements at strategic places. Cumbersome.
There is no immediate window.
But the intellisense is way better and indenting is automatic.

Comment by: LE GUEN Frédéric (29-8-2021 05:58:00) deeplink to this comment

Very good example to learn Office Script.
The way to select the target cells to write the result with getOffsetRange, getResizedRange is confusing for the moment for me. I have to practice ;)
Thanks for sharing this

Comment by: John from Ireland (14-12-2021 01:45:00) deeplink to this comment

There is work ongoing, I believe, to add the Office Scripts engine into Visual Studio. If you open an Office Scripts .ts file, in Visual Studio Code, it does not recognize the ExcelScript engine but there is a way of enabling it.
Here is the instructions for that:
https://github.com/sumurthy/officescripts-projects/tree/main/misc
You can edit the code there but not execute it. You have to copy-paste it back into the Excel Online Code Pane to do that.

The Excel Code editor pane, now, expands across the entire width of Excel.

Comment by: Jan Karel Pieterse (14-12-2021 09:58:00) deeplink to this comment

Thanks John, quite useful :-)

Comment by: Tiago Filipe Sequeira Melo (26-9-2022 16:51:00) deeplink to this comment

Hi,

I have an excel with the following struture:

Column A: Projets Names,
Column B: Jan,
Column C: Feb,
....,
Column M: Dec,

I would like to create a code to protect the sheet/file and have only the current monhth available to fill.

Any idea how can I create it?

Comment by: Jan Karel Pieterse (27-9-2022 10:57:00) deeplink to this comment

Hi Tiago,

Not sure. There is no way to automatically run an Office script when you open a file (yet).
In regular Excel, I would unlock only the cell next to the current month and then protect the worksheet. You can record a script that unlocks a cell and protects the worksheet and then you must adjust it so it does that dynamically (look up the current month in column A, lock all cells, unlock the cell next to the current month, protect sheet). Does not sound too difficult, but I don't have time to write this for you today I'm afraid.

Comment by: Igor Borges (11-11-2022 13:28:00) deeplink to this comment

Hi,

Are there any Office Script functions that allow me to save a copy of the workbook somewhere else? Could you please give me an example?

Comment by: Jan Karel Pieterse (11-11-2022 16:15:00) deeplink to this comment

Hi Igor,

I'm afraid not, Office Script is strictly limited to one workbook. Any file handling must be done using a different system, such as Power Automate.

Comment by: Bram Werbrouck (10-1-2023 09:56:00) deeplink to this comment

How can I change the background color of cells in a range that are not empty?

I can change the backgroundcolor of a specified cell like this:

newSheet.getRanges("A1:A1").getFormat().getFill().setColor("8EA9DB")

But I want to look into a bigger range and color all the cells that aren't empty... Can you please help me with this?

Comment by: Jan Karel Pieterse (10-1-2023 10:14:00) deeplink to this comment

Hi Bram,

I think this should work:

function main(workbook: ExcelScript.Workbook) {
    const sht = workbook.getActiveWorksheet();
    const constantCells = sht.getUsedRange().getSpecialCells(ExcelScript.SpecialCellType.constants)
    const formulaCells = sht.getUsedRange().getSpecialCells(ExcelScript.SpecialCellType.formulas)
    constantCells.getFormat().getFill().setColor("8EA9DB");
    formulaCells.getFormat().getFill().setColor("8EA9DB");
}

Comment by: Bram Werbrouck (10-1-2023 10:24:00) deeplink to this comment

Thx for the quick reply, I have tried your code but some of the not-empty cells shouldn't change from backgroundcolor. So I adjusted your code to the following:

const constantCells = newSheet.getRanges("B7:F8,B12:K19,B21:K28,B30:K37,B39:K40,B10:K10,B9:D9,F9,G7:K9").getSpecialCells(ExcelScript.SpecialCellType.constants)
const formulaCells = newSheet.getRanges("B7:F8,B12:K19,B21:K28,B30:K37,B39:K40,B10:K10,B9:D9,F9,G7:K9").getSpecialCells(ExcelScript.SpecialCellType.formulas)
constantCells.getFormat().getFill().setColor("8EA9DB");
formulaCells.getFormat().getFill().setColor("8EA9DB");

But the I get an error:
Line 41: Cannot read properties of undefined (reading 'getFormat')

Comment by: Jan Karel Pieterse (10-1-2023 11:17:00) deeplink to this comment

Hi Bram,

This may be caused if there are either no cells with formulas or no cells with constants. To cater for that, change the script to:

Comment by: Bram Werbroucck (10-1-2023 11:34:00) deeplink to this comment

Unfortunately, it doesn't work, I have now:

const constantCells = newSheet.getRanges("B7:K10,B12:K19,B21:K28,B30:K37,B39:K40").getSpecialCells(ExcelScript.SpecialCellType.constants)
const formulaCells = newSheet.getRanges("B7:K10,B12:K19,B21:K28,B30:K37,B39:K40").getSpecialCells(ExcelScript.SpecialCellType.blanks)

    if (constantCells) {
     constantCells.getFormat().getFill().setColor("8EA9DB");
    }

if (formulaCells) {
    constantCells.getFormat().getFill().setColor("FFFFFF");
}

But this colors the whole range (data or no data in a cel) in blue...

Comment by: Jan Karel Pieterse (10-1-2023 11:46:00) deeplink to this comment

Hi Bram,

Odd, it works just fine for me. Are you sure the cells are empty? If you pre-select the range in your code and then press goto special, constants then what is selected precisely?

Comment by: Jan Karel Pieterse (10-1-2023 11:47:00) deeplink to this comment

There's a typo in your code, the second IF colors constantCells but should do formulaCells

Comment by: Bram Werbrouck (10-1-2023 13:30:00) deeplink to this comment

You were right about the typo.. I adjusted and give it a proper name (blankCells) with still no luck.. It colors the whole range in blue and I am sure that there are empty cells (when U use the formula: COUNTBLANK, it returns a number)

Is the following line maybe a problem?

newSheet.getRanges("B7:K10,B12:K19,B21:K28,B30:K37,B39:K40").getSpecialCells(ExcelScript.SpecialCellType.blanks)

So this is my current code

const constantCells = newSheet.getRanges("B7:K10,B12:K19,B21:K28,B30:K37,B39:K40").getSpecialCells(ExcelScript.SpecialCellType.constants)
const blankCells = newSheet.getRanges("B7:K10,B12:K19,B21:K28,B30:K37,B39:K40").getSpecialCells(ExcelScript.SpecialCellType.blanks)

    if (constantCells) {
     constantCells.getFormat().getFill().setColor("8EA9DB");
    }

if (blankCells) {
    blankCells.getFormat().getFill().setColor("FFFFFF");
}

Comment by: Jan Karel Pieterse (10-1-2023 14:13:00) deeplink to this comment

Hi Bram,

What happens if you try the same using VBA?

Range("B7:K10,B12:K19,B21:K28,B30:K37,B39:K40").SpecialCells(xlCellTypeConstants).Interior.Color = RGB(123, 123, 123)

NB: setting a fill color of FFFFFF is not the same as no fill!

Comment by: Bram Werbrouck (10-1-2023 15:07:00) deeplink to this comment

I cannot use VBA, because I am using excel in Teams (office 365) and only office script is allowed.

For me, the layout is the most important. If the fillcolor is white (FFFFFF) or there is no fill.

is there an option "nofill"??

Comment by: Jan Karel Pieterse (10-1-2023 15:26:00) deeplink to this comment

Hi Bram,

Sure. The syntax is:

blankCells.getFormat().getFill().clear();

Comment by: Lucas (16-2-2023 15:59:00) deeplink to this comment

Hi Jan,

Thank you for post!
I'd like to ask you if it's possible to open a new browser tab using power automate flow. I have tried different approachs with not success.
Maybe using Office Script into excel?

Thanks and regards!

Comment by: Jan Karel Pieterse (17-2-2023 11:54:00) deeplink to this comment

Hi Lucas,

You should just try, perhaps Office script allows you to open a hyperlink using JavaScript? https://www.geeksforgeeks.org/how-to-open-url-in-new-tab-using-javascript/

Comment by: Tiffany Jo (2-3-2023 07:52:00) deeplink to this comment

Hi Thanks a lot for your post.
I tried to use the office script to split the data in one cell to multiple rows if there are delimiters.
I am using VBA to realise it, I would like to know if office script can realise it as well. Thank you!

Sub SplitLines()
    Dim r As Long
    Dim m As Long
    Dim c As Long
    Dim n As Long
    Dim a() As String
    Dim u As Long
    Dim i As Long
    Application.ScreenUpdating = False
    m = Cells(Rows.Count, 1).End(xlUp).Row
    n = Cells(1, Columns.Count).End(xlToLeft).Column

    For r = m To 2 Step -1
        a = Split(Cells(r, 2).Value, vbLf)
        u = UBound(a)
        If u > 0 Then
            For i = 1 To u
                Cells(r + 1, 1).EntireRow.Insert
                Cells(r + 1, 1).Value = Cells(r, 1).Value
            Next i
            For c = 2 To n
                a = Split(Cells(r, c).Value, vbLf)
                For i = 0 To u
                    Cells(r + i, c).Value = a(i)
                Next i
            Next c
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

Comment by: Jan Karel Pieterse (2-3-2023 11:20:00) deeplink to this comment

Hi Tiffany Jo,

I'm sure it can, as it looks like everything your VBA code does is also possible in Office Script.

The sample code about adding a list of range names I posted above, should enable you to figure out how looping works in Office script!

Comment by: Vali Gibson (24-3-2023 22:57:00) deeplink to this comment

Hi Jan,

Thank you very much for you post, it has been very helpful.

I am having a really hard time trying to create multiple selection for dropdown lists using Excel Online. Since only Office Script is allowed, I have not been able to figure out a code that works, like there is for using VBA.
Is it possible to create multi-select dropdown list with Office Script?

Comment by: Jan Karel Pieterse (27-3-2023 10:36:00) deeplink to this comment

Hi Vali,

Given that Office On-line does not allow you to add controls to worksheets such as the combo-box or a listbox, I expect the answer is no.

Comment by: Ana (20-4-2023 22:17:00) deeplink to this comment

Looking for an office script to remove all formulas and data connection from an excel file and keep the values

Comment by: Jan Karel Pieterse (24-4-2023 11:34:00) deeplink to this comment

Hi Ana,

Have you tried to record those actions using Script recorder?

Comment by: James Lewis (3-8-2023 13:59:00) deeplink to this comment

I am looking to run a script to do something very simple whereby every week I need to increase the range of a formula by 1 cell from another sheet within the excel file so that it can compare previous year month to date against current year month to date, but I don't want to have to go through every formula for YTD, QTD, MTD and MAT and do this manually, but I really can't work out how I would run a script to do this.

Can you help with this?

Comment by: Jan Karel Pieterse (3-8-2023 14:32:00) deeplink to this comment

Hi James,

Thanks for the question. I expect this shouldn't be too much of a problem, but I would need to have an idea of the exact setup of the sheet. Do you want to "take this off-line" and discuss it directly rather than through my site perhaps?

Comment by: Paul (24-11-2023 13:37:00) deeplink to this comment

I want to delete all rows present in my worksheet using script. The current script deletes all rows one by one, I want to do it in one step

In vba I can easily do that by finding the last row and then providing the range but in office script I'm unable to find it.

Rows("3:" & lastRow).Delete
Ineed same type of code for script

Comment by: Jan Karel Pieterse (24-11-2023 19:53:00) deeplink to this comment

Hi Paul,

I think like this:

function main(workbook: ExcelScript.Workbook) {
// Get the active cell and worksheet.
workbook.getActiveWorksheet().getUsedRange().getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
}

Comment by: Hemal (14-5-2024 09:13:00) deeplink to this comment

I want to remove all blank spaces within selected cell range.

Comment by: Jan Karel Pieterse (16-5-2024 15:47:00) deeplink to this comment

Hi Hemal,

Do you mean you want to clear the content of cells that only contain one or more space characters?

Comment by: Hans Hallebeek (20-8-2024 08:16:00) deeplink to this comment

Hi Jan Karel,
your code for "The function getLastRow shown below, returns the last used row of any range (assuming the cell on row 1048576 is empty!)"
Why not add a simple prompt 'No empty rows available!'?

Comment by: Jan Karel Pieterse (20-8-2024 16:27:00) deeplink to this comment

Hoi Hans,

Ik heb de pagina aangepast.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.