cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
katieb96
New Member

Deleting (mostly) empty rows from an Excel table

hello folks! i currently have a table with several empty rows or rows with only one cell that i need to automatically delete. the table is created via an office script run as part of a flow, but either script code or a power automate cell could work with my solution. does anyone know how to make this work? thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
sumurthy
Microsoft
Microsoft

This Office Script can help. 

It uses few key columns that are relevant to me. You can change that to suit your needs. In my case, I'm using first and second column values to check if it is empty. 

To check if all columns are empty, you'd have to repeat that for all fields. If your column count is large, then you can loop through and check each one and make it simple that way. 

 

See below for the code, before and after view of my test data. 

 

Please let me know if you need more help. 

 

 

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let table = selectedSheet.getTables()[0];
  let tableDataRange = table.getRangeBetweenHeaderAndTotal();
  let dataValues = tableDataRange.getValues();

  let removed = 0;
  // Important that you go from bottom to top to account for removing correct rows. 
  for (let i = dataValues.length-1; i >=0 ; i--) {
    let [event, date] = dataValues[i];
    if (event === '' || date === '') {
      // tableDataRange.getRow(i).getFormat().getFill().setColor('Yellow');      
      table.deleteRowsAt(i);
      removed++;
    }
  }
  console.log(`Removed ${removed} rows.`)
}

 

 

 

Before

sumurthy_0-1605563275676.png

 

After

sumurthy_1-1605563339835.png

 

 

View solution in original post

2 REPLIES 2
sumurthy
Microsoft
Microsoft

This Office Script can help. 

It uses few key columns that are relevant to me. You can change that to suit your needs. In my case, I'm using first and second column values to check if it is empty. 

To check if all columns are empty, you'd have to repeat that for all fields. If your column count is large, then you can loop through and check each one and make it simple that way. 

 

See below for the code, before and after view of my test data. 

 

Please let me know if you need more help. 

 

 

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let table = selectedSheet.getTables()[0];
  let tableDataRange = table.getRangeBetweenHeaderAndTotal();
  let dataValues = tableDataRange.getValues();

  let removed = 0;
  // Important that you go from bottom to top to account for removing correct rows. 
  for (let i = dataValues.length-1; i >=0 ; i--) {
    let [event, date] = dataValues[i];
    if (event === '' || date === '') {
      // tableDataRange.getRow(i).getFormat().getFill().setColor('Yellow');      
      table.deleteRowsAt(i);
      removed++;
    }
  }
  console.log(`Removed ${removed} rows.`)
}

 

 

 

Before

sumurthy_0-1605563275676.png

 

After

sumurthy_1-1605563339835.png

 

 

View solution in original post

katieb96
New Member

it works perfectly! thank you so much!

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (53,652)