Table formatting to remove certain number of rows

[E.T.S]

Hello,

I have a table that looks like this:

I would like the data to be near the column headings, and also have the ‘Totals’ only one line away from the data like this:

The data could be any number of rows below the Column names, and the ‘Totals’ can be any number of rows below the data. The column headings will always be in row 6.

Please advise how I could achieve this

Thanks

[ashokkarale]

@E.T.S ,

If your data don’t have any formatting to preserve I would suggest to Read Range the data into DataTable

1. Use Filter DataTable and Remove Rows which are blank at first cell. This will remove all the unnecessary empty cells/rows.

2. Add empty row to DataTable.

3. Add one more row to DataTable with all columns empty except Totals & It's Value (Should be calculated with some simple logic)

4. Everything is ready. Just clear everything on the Worksheet and use WriteRange to write modified data.

Thanks,
Ashok

[E.T.S]

Thank for your reply!

I am unsure how to calibrate logic to insert data row above row that contains ‘Totals’

Cheers

[ashokkarale]

Use Activities - Add Data Row and give array as empty like {"",""} add “” for number of columns you have.

For Total column use Add Data Row once again and pass the array {"","","Totals","your total"} this way.

This will add the row at bottom of the DataTable

[pikorpa (Piotr Kołakowski)]

Hey @E.T.S
Try this workflow:
BlankProcess112.zip (9.4 KB)
I used this method:
filteredDataTable = originalDataTable.AsEnumerable().Where(Function(row) Not row.ItemArray.All(Function(field) field Is Nothing OrElse String.IsNullOrWhiteSpace(field.ToString()))).CopyToDataTable()