How to delete Rows containing blank spaces?

[Kavyasri (Kavya)]

Hi everyone,
I have one doubt regarding deleting rows containing blank spaces.I tried one process but I took 2 different sheets(one is blank) so that the output comes in the 2nd blank sheet…But I want the output to be present in the same sheet.This is the workflow which I tried.
usecase3.zip (2.5 KB)
Input:

Expected Output :

Can anyone please suggest me any idea so that the result should come in the same sheet…

Thanks & Regards.

[Yoichi]

Hi,

I think it should be easier to use LINQ Where method. Can you try the following?

testdt2 = testdt.AsEnumerable.Where(Function(r) r("Department") IsNot Nothing AndAlso (not String.IsNullOrWhiteSpace(r("Department").ToString))).CopyToDataTable

Main.xaml (8.3 KB)

Note: Your workflow lacked DataSetExtensions reference due to UiPath problem. I modified it in above Main.xaml.

Regards,

[ImPratham45 (Prathamesh Patil)]

Hi U can use below VBA code and use it in workflow - use Invoke VBA

Sub Delete_Rows_Based_On_Value()

Dim ws As Worksheet

'Set reference to the sheet in the workbook.
Set ws = ThisWorkbook.Worksheets(“Sheet1”)
ws.Activate

'Clear any existing filters
On Error Resume Next
ws.ShowAllData
On Error GoTo 0

'1. Apply Filter
ws.Range(“A1:B1000”).AutoFilter Field:=2, Criteria1:=“”

'2. Delete Rows
Application.DisplayAlerts = False
ws.Range(“A1:B1000”).SpecialCells(xlCellTypeVisible).Delete
Application.DisplayAlerts = True

'3. Clear Filter
On Error Resume Next
ws.ShowAllData
On Error GoTo 0

End Sub

Here u found ur xaml

usecase3.zip (9.1 KB)

[Cristian_Bardas]

Hi,

Keep it simple, filter the data using a LINQ select:

(From Row In dtTable.Select() where Not String.IsNullOrEmpty(Row.Item(“Department”).ToString)select Row).CopyToDataTable

Full description:

1. Read range - get the Excel data in a datatable
2. Filter the datatable with the above LINQ select
3. Delete Range - clear data in Excel (you need to clear the data, otherwise there is a risk to have more rows than expected)
4. Write range - put the filtered data back in Excel

Here’s a picture of the flow:

Hope this helps,
Cristian

[Kavyasri (Kavya)]

Hi @Yoichi, I cannot see code in the workflow…Activity is missing

[Kavyasri (Kavya)]

@Cristian_Bardas Thanks for the reply…I am getting this error in the Linq select statement

[Yoichi]

Hi,

Did you open the xaml file from download folder?
If so, can you try to move the above Main.xaml to your original project folder? (if necessary, please rename it before move.)

Regards,

[Kavyasri (Kavya)]

Thanks for the help @ImPratham45 …I am not familiar with VB but I will try this

[ImPratham45 (Prathamesh Patil)]

Yes sure

[Cristian_Bardas]

You need to make sure you have imported the following namespaces, sometimes UIPath forgets about them …

System.Data.DataSetExtensions
System.Linq
System.Linq.Expressions

After you have imported them, if you still get an error, save your workflow, close and reopen, the error will be gone.