Forum Discussion

Copper Contributor

Jul 17, 2023

Searching multiple worksheets for different numbers simultaneously in Excel

Is it possible to search multiple worksheets for different numbers simultaneously in Excel? Using CTL-F isn't working for me. For example, can I search 10 different worksheets all at once for t...

excel

SergeiBaklan

Diamond Contributor

Jul 19, 2023

KennyK360

Looks like totally different task. Other words, delete in the range entire rows which have cells with error in one or more columns, correct?

But that can't be done with formula, VBA or ExcelScript.

KennyK360

Copper Contributor

Jul 19, 2023

Yes, that is correct (delete in the range entire rows which have cells with error in one or more columns). It can't be done with formula, VBA or ExcelScript...are you saying there are no options to achieve this task in Excel? Thank you for your time.

SergeiBaklan

Diamond Contributor

Jul 20, 2023

KennyK360

IMHO, that could be done with VBA or ExcelScript. I'll try bit later the latest.

KennyK360

Copper Contributor

Jul 21, 2023

Thank you so much for your help Sergei.

SergeiBaklan

Diamond Contributor

Jul 22, 2023

KennyK360

That could be like

function main(workbook: ExcelScript.Workbook) {

    const range = workbook
        .getActiveWorksheet()
        .getUsedRange()

    const nRows = range
        .getRowCount()
    
    let j = 0;
    let nErrors: number[] = [];

    for( let i = 0; i < nRows; i++) {
        if ( range
                .getRow(i)
                .getValueTypes()
                .toString()
                .includes("Error")
        )  nErrors.push(i) 
    }

    nErrors.map( x => {
        range
            .getRow(x - j)
            .getEntireRow()
            .delete(ExcelScript.DeleteShiftDirection.up);
         j++
    } )
}