Remove multiple empty rows at once in ExcelJS

Question

I have the following function that works well for small number of rows:

async function RemoveEmptyRows() {
  await Excel.run(async (context) => {
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    var selectedRange = context.workbook.getSelectedRange();
    var surroundingRegion = selectedRange.getSurroundingRegion();

    var firstCell = selectedRange.getCell(0, 0);
    firstCell.load(['columnIndex']);
    surroundingRegion.load(['rowCount', 'columnCount']);
    

    await context.sync();
    var newRange = sheet.getRangeByIndexes(0, firstCell.columnIndex, surroundingRegion.rowCount, 1);
    newRange.load(['address', 'rowCount', 'values']);
    await context.sync();
    
    var i = 0;
    var loopRange = newRange.rowCount;
    var counter = 0;
    var cellText = "";

    while (i < loopRange) {
      cellText = newRange.values[i][0];

      if (cellText == "") {
          var entireRow = sheet.getRangeByIndexes(i - counter, 0, 1, surroundingRegion.columnCount);
        entireRow.delete(Excel.DeleteShiftDirection.up);
        counter++;
      }
      i++;
    }

    await context.sync();
  });
}

The problem with this function is that it shifts the entire row up, row by row. There is another function (modified from ScriptLab) to remove duplicates, that works well with a much larger number of rows.

async function RemoveDuplicates() {
  await Excel.run(async (context) => {    
    const sheet = context.workbook.worksheets.getActiveWorksheet();
    var selectedRange = context.workbook.getSelectedRange();
    var firstCell = selectedRange.getCell(0, 0);
    var surroundingRegion = selectedRange.getSurroundingRegion();

    firstCell.load('columnIndex');
    surroundingRegion.load('address');

    await context.sync();
    var columnIndex = firstCell.columnIndex;
    
    const deleteResult = surroundingRegion.removeDuplicates([columnIndex], true);
    deleteResult.load();
  });
}

Is there a way to create something similar to the RemoveDuplicates function, but for EmptyRows?

Thanks.

Could you clarify why shifting up, row by row, is a problem? And what is it about the RemoveDuplicates function that you like? — Rick Kirkham, Apr 20 '21 at 20:19
It works well for about 1000 rows. It takes a lot of time if I have 10K rows. If I use Ctrl + - in Excel to open the Delete window, there is an option to shift cells up and left, but also to delete the entire row. The RemoveDuplicates function is much faster than RemoveEmptyRows. — Tomasz Decker, Apr 21 '21 at 07:49

score 1 · Accepted Answer · answered Apr 21 '21 at 02:15

1

So far Excel JS do not have a DeleteEmptyRows(), but it sounds like a good suggestion, we will consider creating this API in the future.

Is that OK to sort the range? if yes, here is a workaround. Firstly, sort the range, therefore the empty rows would stay together, then create a range that includes the empty rows, after that, you can call range.delete(up) to remove the empty rows.

answered Apr 21 '21 at 02:15

Raymond Lu

2,178
1
6
19

1

Yes, it will be an option to consider. I was thinking about adding each empty row number to a list and then remove them at once by deleting the entire row like you can do in Excel with the Ctrl + - button. But there is no such option right now. Your option seems quite reasonable. – Tomasz Decker Apr 21 '21 at 07:54
@Raymond its ('up') – FreeSoftwareServers Mar 18 '22 at 17:53

Remove multiple empty rows at once in ExcelJS

1 Answers1