Insert Excel data before/after certain index/value


Hi All,

I have one excel sheet and it looks like this!Capture

Now I have filter out data on status i.e. Pending.
For that I am using select query.

After Filtering out I need to put the data in same excel file below “Pending” line as shown in below image.

and resultant file should look like as below image.Capture2

I am attaching excel file.Book2.xlsx (8.7 KB)

Need Solution for this.



There are a couple approaches you can take.

It might be best to manipulate the excel with a datatable (assuming “Pending” is at the bottom of the datatable variable), so if you initially use Read Range, you can do it like this:

Filter to Pending rows as an Array of DataRows
Inside a generic For each (DataRow TypeArgument), Use Add Data Row using ArrayRow property like rowVar.ItemArray
inside the same For each, use Delete Data Row using Row property
Clear sheet, then Write Range to same sheet (or Write to a new sheet or file)


So, essentially, you are adding the row to the bottom of the table, then deleting the row from the top.

Additionaly, another approach would be to find the index of the Pending row, which you can do in the same way you filtered the other rows but just look for “Pending” in first column.
To find index you can do this:

dt1.Rows.IndexOf(arrayVar(0)) // use (0) because it is an array of rows

Then you can use that index in the Write Range, et cetera

I hope this helps you in the right direction.



Thanks for reply.

I was doing 2nd approach.
writing with index keeps the earlier data and rewrite whole data.

My data are coming like below image.Capture3

Can you give sample for this.



I put this together quick. I have 2 versions that I left there incase you were interested but the only difference is that the other one not being used will move up the Pending row (which leaves the colored row in the wrong place).

Here is the sample that performs your requirements: Sample1.xaml (19.9 KB)

Here is the output I received:

In summary,
—Read Range
—Store pending rows, count, rows as a table, and pending area index
—Loops through the pending rows and removes them from table
—Writes back the new table without the pending rows with .Take() to only go up to the Pending area so it stays in the right place
—Writes the pending table after the pending area

Flaws: If there are existing pending items already in pending area, then they will be overwritten. This can be fixed though by adjusting the pendingIndex assignment to be below the last row in the pending area.