Delete all rows except the header in 365 Excel

How To
1

Hello guys,

hope you are doing well!
I got a question, I want to delete all the rows except the first row(the header). But how do I do this? I tried several thing with the Row ID, but I can’t make it work. For example, I have 5 rows now in my sheet, I tried to select the function: Total Number of bundles. This gives a value of 5, but it doesn’t delete all 5 rows.

Attached are the screenshots of the scenario.

Capture 1
Capture 1916×738 86.5 KB

Capture 2
Capture 21476×312 38.5 KB

2

Hi. I think you need delete one my one to let the first one there…

Thanks, Helio!
Wemakefuture
If you have questions reach out :wink:

1 Like
3

Hey @Zavatos ,

If you can not loop through all the rows and delete them (what @Wemakefuture suggested, which would be the easier solution), you could use the “Make an API Call” Action from the Excel Module.

There you would need to call the “Range: delete” endpoint:

POST /me/drive/items/{id}/workbook/names/{name}/range/delete
POST /me/drive/root:/{item-path}:/workbook/names/{name}/range/delete
POST /me/drive/items/{id}/workbook/worksheets/{id|name}/range(address='<address>')/delete
POST /me/drive/root:/{item-path}:/workbook/worksheets/{id|name}/range(address='<address>')/delete
POST /me/drive/items/{id}/workbook/tables/{id|name}/columns/{id|name}/range/delete
POST /me/drive/root:/{item-path}:/workbook/tables/{id|name}/columns/{id|name}/range/delete

For instance with that one it would be roughly like:

POST /me/drive/items/{file-id}/workbook/worksheets/Sheet1/range(address='Sheet1!B1:ZZ99')/delete

Do not hesitate to contact me when you’re having further questions :slight_smile:

Christoph Gerl
Funkenwerfer Process & CRM-Automation

1 Like
4

@Wemakefuture and @Funkenwerfer thanks for your comments! I appreciate it!

@Funkenwerfer I tried your API call, but I get a weird issue that there is no function called: range. I looked it up in the Docs of the API, but I saw the function was there.
Here is the API link I made:
GET /v1.0/me/drive/root:/BroodjeBen.xlsx:/workbook/worksheets/Data Input/range(adress=‘Data Input!B2:F30’)/delete

I got the following error:
The operation failed with an error. [400] Resource not found for the segment ‘range’.

Update:
I made the API link like this: /v1.0/me/drive/root:/BroodjeBen.xlsx:/workbook/worksheets(‘Data Input’)/range/‘Data Input!B2:F4’/delete

Now it says: The operation failed with an error. [400] Resource not found for the segment ‘‘Data Input!B2:F4’’.

But there is data In B2:F4
What did I do wrong?