But I feel as if I’m missing something out.
To be precise, my goal is to delete some rows from an Excel sheet.
At first, I read the original sheet into datatable d1.
Now, I need to apply criteria to delete some rows from dt1.
I tried using .Select, however the column I need to search has numbers and texts making my “like” invalid.
So I thought about this second approach so I could apply the criteria first, then get the whole datarow. It doesn’t seem very pratical having 3 datables in order to delete some rows from Excel Sheet
I think you need to simply use Add Data Row to the data table, then re-filter to your array of rows again and it will have that data row included. Now, saying that, I’m not sure if you are doing this inside a ForEach that references the rows, then you might need to restart the loop because the data table has changed at that point.
If you want to keep your loop, then you will need to duplicate your data table so you can change the data.
There are other ways too but you would need to calculate the row number in Excel (maybe dt1.Rows.Count+2, or whatever), and use Write Cell or Write Range with those coordinates. This solution would also require you to convert the column index to a char (like Char(65+index) )
Thanks for sharing @balupad14.
I am sure this will be very useful at some point. But unfortunately I can’t use the “Remove data row” feature as my select won’t work. I think the best scenario would be making it work (but I am still not sure if it’s even possible).
Trying your first solution (add to collection) I get an exception due to the collection not being initialized:
And here’s my collection:
LinhasParaDeletar (RowsToDelete) being DataRow
And row being my “index” in for each row for dt1.
As I was saying before, my goal is to delete all rows that start with anything different than “3” or “5”.
This is a sample of my column: