Delete rows in excel if value in a column is "D"

[Brennan_Montoni]

Hi all,

How would I delete rows in excel if value in a column IS NOT “D”?

Thanks in advance!

[Eric_Wong_Test (Eric Wong)]

dt2 = dt.AsEnumerable.Where(Function (x) not x(“Form Type”).ToString = “D”).CopyToDataTable

[Brennan_Montoni]

@Eric_Wong_Test, What tool would be used for this?

[Eric_Wong_Test (Eric Wong)]

[Brennan_Montoni]

@Eric_Wong_Test, would this delete the rows directly from the excel file and keep the data in the same format. One of the other fields is a clickable link to a website that needs to remain.

[NIVED_NAMBIAR (NIVED N)]

Hi @Brennan_Montoni

It is actually filtering the rows from the datatable

After doing this , write this datatable into the excel file and you can see that results will.be displayed in excel file.

Regards

Nived N

Happy Automation

[Brennan_Montoni]

thanks @NIVED_NAMBIAR. is there any way to just delete the records that DO NOT contain a “D” in the Form Type column?

[Eric_Wong_Test (Eric Wong)]

Yup, Find the Index that contains “D”
Then, loop the index and using Remove Data Row

[prasath_S]

Hi @Brennan_Montoni

You can use invoke VBA activity,

Sub macro()
Sheets(“yoursheetname”).select
Range(“A1:H” & cells(rows.count,1).end(xlup).row).autofilter field:=5 ,criteria1:=“D”
Range(“A1:H” & cells(rows.count,1).end(xlup).row). specialcells(xlcelltypevisible). entirerow.delete
Cells.autofilter

End sub

[Jayesh_678 (Jayesh)]

Hi,

Please check this Delete value from column.zip (9.0 KB)
It works for me. Let me know if you faced any issue.
Thanks

[Brennan_Montoni]

@Jayesh_678 , yours works perfectly. i tried replicating but its not inputting the data on sheet2 for some reason. See my data and file in the folder below:

Delete value from column.zip (47.9 KB)

[Jayesh_678 (Jayesh)]

Hi @Brennan_Montoni,

Actually there is a extra space with "D ". So i used trim function. It works.Delete value from column.zip (47.9 KB)

If it works for you, don’t forget to mark it as a solution…

Thanks

[Brennan_Montoni]

@Jayesh_678 , I was able to get it to work but I need the active links present because the next step is to click on each of them and scrape data back into the excel file. Is this possible?

[Jayesh_678 (Jayesh)]

Use for each row

Type into :- row.item(“SEC Link”)

Scrape data using get text.

Write it into the excel.

If it not works, I will suggest to create a different question for it with different tag line, so any1 can help you.

[Brennan_Montoni]

@Jayesh_678 , thank you. Before I start going through each row, clicking the SEC Link, and scraping, I need to get the sheet to only contain rows where Form Type = “D” and include the links that can be clicked. Do you know how I could accomplish this?

Currently, the output does not contain links:

[Jayesh_678 (Jayesh)]

We are printing the data on sheet2. That data table is having the expected output with SEC Link.

Please check it once the write range activity for sheet 2(we are using it @ the end)

Thanks

[Brennan_Montoni]

@Jayesh_678 , yes, I see the data on sheet2 but the links are not active. I need to be able to click on them and go to the webpage.

[Jayesh_678 (Jayesh)]

Okay, after excel application scope, we are using read range, check preserve format option from properties and try.

Thanks

[Brennan_Montoni]

@Jayesh_678 . I tried that but still getting same result