Automated Excel Script for Deleting Rows Containing Highlighted Cells

Does anyone have a solution for removing rows from a table in an office script that contain highlighted cells? I attempted to filter rows with highlighted cells using the recorder, but it showed 'This action is not yet recordable.' If you have a successful script that accomplishes this task, please share!

Current script:

function main(workbook: ExcelScript.Workbook) {
    let conditionalFormatting: ExcelScript.ConditionalFormat;
    let selectedSheet = workbook.getActiveWorksheet();
    
    // Create preset criteria from range B:B on selectedSheet
    conditionalFormatting = selectedSheet.getRange("B:B").addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
    conditionalFormatting.getPreset().getFormat().getFont().setColor("#9C0006");
    conditionalFormatting.getPreset().getFormat().getFill().setColor("#FFC7CE");
    conditionalFormatting.getPreset().setRule({criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues,});
}

I need to figure out how to filter or delete rows containing highlighted cells. Any help is appreciated!

I encountered an issue with the recorder saying the action is not yet recordable.

exceltypescriptoffice-scriptsms-office-script

Answer №1

  • Record Actions does not function properly when using the Filter by Cell Color operation.
  • To filter out duplicate cells, utilize 
    filterOn: ExcelScript.FilterOn.cellColor
    .
function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // obtain data range in Column B
    let dataRng = selectedSheet.getRange("B:B").getIntersection(selectedSheet.getUsedRange());
    // apply Conditional Formatting
    let condFormat = dataRng.addConditionalFormat(ExcelScript.ConditionalFormatType.presetCriteria);
    let cfFormat = condFormat.getPreset().getFormat();
    cfFormat.getFont().setColor("#9C0006");
    let colorToFilter = "#FFC7CE";
    cfFormat.getFill().setColor(colorToFilter);
    condFormat.getPreset().setRule({ criterion: ExcelScript.ConditionalFormatPresetCriterion.duplicateValues });
    // apply auto-filter
    selectedSheet.getAutoFilter().apply(selectedSheet.getRange("B1"));
    // filter duplicated cells with specified fill color
    let filterCriteria = {
        filterOn: ExcelScript.FilterOn.cellColor,
        color: colorToFilter
    };
    selectedSheet.getAutoFilter().apply(dataRng, 0, filterCriteria);
    let visRng = dataRng.getResizedRange(-1,0).getOffsetRange(1,0);
    // highlight duplicated rows with Green color
    visRng.getEntireRow().getFormat().getFill().setColor("#00FF00");
    // remove duplicated rows
    visRng.getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
    // Toggle auto-filter on selected sheet
    selectedSheet.getAutoFilter().remove();
}

Similar questions

If you have not found the answer to your question or you are interested in this topic, then look at other similar questions below or use the search

Rotating display for showcasing various portfolios

I'm facing an issue with my portfolio images carousel using a map in Bootstrap. When I navigate from one portfolio (e.g. image 4) to another (which has only one image), the carousel shows up blank because the active carousel-item is at index 3 (image ...

javascriptreactjstypescriptbootstrap-5

Issue with React.js code not being detected in TSX file (Visual Studio 2015 Update 1 RC)

Currently, I am utilizing Visual Studio 2015 with update 1 release candidate. Interestingly, I have managed to successfully incorporate React.js code and syntax highlighting within a .JSX file. However, when it comes to a .TSX file, nothing seems to be wor ...

reactjstypescriptvisual-studio-2015

Guide on transitioning an Angular 4 project created in Visual Studio 2015 to Angular 6 with Visual Studio Code

Currently, I am collaborating on an Angular 4 project that utilizes a web API in Visual Studio 2015 update 3. This project serves as an ERP solution. My goal is to enhance the project by updating it to Angular 6, with Visual Studio Code as the primary too ...

angulartypescriptvisual-studio-codeangular6

Struggling to transfer array data from service to component

I am currently working on passing an array from service.ts to a component. My goal is to display the array elements in a dialog box. However, I encountered a Typescript error TypeError: Cannot read property 'departmentArr' of undefined. I am str ...

angulartypescriptangular-material

Click on the element found by the class name using Selenium

I'm facing a challenge in identifying the correct item to interact with during my click event. In the given scenario, I believe I have located the right item to click on. By observing the button class "css-z4yfkz," I can successfully click it. Howeve ...

excelvbaselenium-webdriverselenium-chromedriver

You cannot assign multiple properties with the same name to an object literal

I am facing an issue with two validator functions in my TypeScript file. The first validator checks if a user enters a new password same as the old one, displaying an error if so. The second validator ensures that "new password" and "confirm password" must ...

angulartypescriptvalidation

Angular 2 Unit test issue: Unable to resolve parameters for 'RequestOptions' class

I am currently working on testing a simple component that has some dependencies. One of the requirements is to provide certain providers for the test. /* tslint:disable:no-unused-variable */ import { By } from '@angular/platform-browser&ap ...

unit-testingtypescriptangularjasmine

Postponing a function invocation in an Angular/TypeScript application

I am facing a challenge in my Angular project. I have a form on one of my pages that allows users to submit data which is then sent to the database. However, after submitting the data, I need to delete the user's information from one table and insert ...

javascriptangulartypescript

Troubleshooting problem with Electron and TypeScript following recent updates

I recently made updates to my small Electron project using Electron and TypeScript. Here's the code causing issues: dialog.showOpenDialog({}, (files) => { if(files && files.length > 0) { fs.readFile(files[0], 'utf8' ...

node.jstypescriptelectron

The angular 5 application encountered an issue where it was unable to access the property 'singlePost' due to a null value, resulting

When using the once method to fetch data from the Firebase database, everything works correctly. However, when I try to use the on method, I encounter an error that says: ERROR TypeError: Cannot read property 'singlePost' of null. How can I prope ...

javascriptangulartypescriptfirebase

Issue encountered while attempting to bind to formControl

I've searched through numerous solutions but still can't figure out why my code isn't working. I made sure to import ReactiveFormsModule, following the example in the Official Docs Example. Error: Can't bind to formControl since it isn ...

angulartypescript

When calling EntityManager.save(), any fields that are not provided will be saved as null values

Currently, I am using @nestjs/typeorm": "^8.0.2 in conjunction with Postgres and encountering an unusual issue that seems like unexpected behavior: When attempting to save a partial entity, the fields I specify are saved correctly, but the resul ...

typescriptpostgresqlnestjstypeorm

Decorators do not allow function calls, yet the call to 'CountdownTimerModule' was executed

While building production files, the aot process is failing with this error message: Function calls are not supported in decorators but 'CountdownTimerModule' was called. I run the build command using npm run build -- --prod --aot and encounter ...

angularangularjstypescriptangularjs-directive

How come I am unable to fetch classes and enums from a namespace?

When using Typescript with pg-promise, I am facing an issue where I can't import the classes and enums as I normally would. Typically, when working with a library, I import a type, use it, and everything functions properly. However, in the snippet bel ...

typescriptpg-promise

Tips for dividing by a large number

I am currently attempting the following: const numerator = 268435456; const denominator = 2 ** 64; const decimalFraction = numerator / denominator; In order to achieve this, I have experimented with utilizing the code provided in this link: : const rawVal ...

javascriptreactjstypescriptcreate-react-appbigint

What is the best way to create a function that requires an argument in TypeScript?

I'm looking to bring in a module that requires an argument in Typescript. This is how it looks in javascript: const cors = require('cors')({origin: true}); // JS What would be the equivalent syntax in Typescript? ...

javascripttypescript

The parameters 'event' and 'event' are not compatible with each other

I'm currently working on a page that involves submitting a form: import React from 'react'; import Form from 'react-bootstrap/Form'; import { useSignIn } from '../../hooks/Auth/useSignIn'; import { useHistory } from &apos ...

javascriptreactjstypescript

Utilize Java to Migrate Excel Data into Mongodb

Recently attempted to import Excel data into Mongo db using the document format below: [ {"productId":"", "programeName":"", "programeThumbImageURL":"", "programeURL":"", "programEditors":["editor1","editor2"], "programChapters":[ { "chapterName":"chapter ...

javajsonexcelmongodbapache-poi

Utilizing the subclass type as a parameter in inheritance

Looking for a way to restrict a function in C# to only accept classes of a specific base class type? In my current implementation, I have a base class (which can also be an interface) and n-classes that extend it. Here is what I am currently doing: abstr ...

c#typescript

Generate a pre-signed URL for an AWS S3 bucket object using Typescript in NextJS, allowing for easy file downloads on the client-side using @aws-sdk/S3Client

In the utilization of v3 of the S3Client, it appears that all existing examples are based on the old aws-sdk package. The goal is for the client (browser) to access a file from S3 without revealing the key from the backend. From my research, it seems tha ...

typescriptnext.js