Sort and remove lines in Excel

I need to sort and remove duplicates and emtpy lines. How would i do that best?
I want to remove the blank lines, and dublicates, well all i want left is
nothing else.

1 Like

Dim distinctDT As DataTable = myDT.DefaultView.ToTable(True, “Product”)
and after you can use sort data table

once after getting the datatable named
—we can first use REMOVE DUPLICATE ROWS activity and get the output named dt to remove the duplicate rows

—then use a assign activity like this
To remove blank rows and order them

dt = dt.Select(“[Product] <> ‘’ “).OrderBy(Function(a) a.Field(of String)(“Product”).ToString).CopyToDatatable()

This dt will
Have the records we require

Cheers @Doktorgud

1 Like

Hi, thanks for great support.
I cant get it to work tho, im doing something wrong/missing something.

1 Like

If possible can I see the expression mentioned and error you got

Cheers @Doktorgud

could that be the problem? (friday i could be to tierd)

1 Like

Yah that’s the issue it’s actually single quotes with no value (represents null Value)
[Product] <> ‘’
Like this
dt.Select(“[Product] <> ‘’ “).OrderBy(Function(a) a.Field(of String)(“Product”).ToString).CopyToDatatable()

Cheers @Doktorgud

this will help you to remove duplicates from your datatable dataTable new_dt =old_dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)(“columnWithDuplic”)).Select(Function(g) g.First).CopyToDataTable

to remove empty cells you can do this
array_dataRow = DataTableVar.Select("Prouducts = “” ")
for each loop inside loop use invoke method to Remove to delete rows
cheers! @Doktorgud

So what would the best way to get arround the “NULL” value?

Better mention mike this
dt.Select(“[Product] <> string.Empty“).OrderBy(Function(a) a.Field(of String)(“Product”).ToString).CopyToDatatable()

Cheers @Doktorgud

Im getting error…“Disallows implicit conversion of Boolean to string”…
Please guide

Its upset again :wink: maybe i should try something else, liked this tho, seemed like a good soulotion.

“Assign: Cannot find column [string.Empty]”

did you try my solution! @Doktorgud

On my way now, hold on :smiley:


So assign? but under read range? and remove the rest?

new_dt =old_dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)(“columnWithDuplic”)).Select(Function(g) g.First).CopyToDataTable

why are you using duplicate rows Activity?
do this

  1. Read Range
  2. then remove duplicates use assign activity thats it
  3. and use remove rows with empty cell value cheers


I think i tried to many things and made a mess, trying this now, ill be back! (BIG THANK YOU to ALL of YOU)

i get this:

you need to change the column name bro!
instead of columnWithDuplic
go through the code