How to filter a data table by using add data row?




I want to filter a data table without using the filter in Excel. I have found one way to do it, but I think it is not the most efficient way.

  1. First I use Read Range to store the Excel table in a Data table variable, lets call it DTmain.
  2. Second, I assign to variable “key” the value I want to filter my table with,
  3. Then I build a data table, named DTfilter
  4. I then use a For each row loop to iterate through the rows in DTmain. If the row item in column “x” which contains the key variables matches, I use Add data row to the table I built in step 3, DTfilter.
    The output of this will be a filtered data table based on the key

Regarding step 4, I use Array row input and refers to objects like this: row.item(“column name 1”), row.item(column name 2), and so on…
With many colums this Array row input box gets quite long…

So my questions are:
Is there a way to just add the whole row?
When I use Add data row, should I use the Array row input, or the DataRow input?

Also, when I build my data table, DTfilter, I create as many columns as the original data table has, but I feel this gets quite static. Lets say a new column gets added in Excel, then I would have to make changes in my Data table, and maybe update colum name and so on…

Would be glad if someone could share their best practice on this, or refer to similar topics where this topic is discussed. Thank you.


I guess this should do everything

DtFilter= new DataTable - initialize datatable
DtFilter=DTMain.Clone - Copies DTMain structure
DtFilter=DtMain.AsEnumerable().Where(Function(row) row(“x”).ToString.Contains(key)).CopyToDataTable()
Copies rows where column x contains key



Please do follow few tutorials below