How to delete entire Row if a specific column has empty cells

[Tarif_Mohammad (Parth)]

Hello Everyone,

I have the following datatabel

I need to filter the red marked column. The condition is if the Column has any “Empty” or “Blank” cell then that entire row must be deleted. At the moment i m being able to do it using VB Script.
Now the challange is that the table does not always have “Empty” or “Blank” cell, so at that moment the VB Script is throwing error ofcourse. I tried to use the Filter Datatable activity but its only removing the rows based on “Empty” cells but not the “Blank” cells. Is there any way to check it inside the VB script first if any “Empty” or “Blank” cell is available and if available only then try and delete the rows so that irrespective of any “Empty” or “Blank” cell is available or not the process will not stop. The VB Script that i m using are:

For Empty Cell:
Sub DeleteLeerRows()
With ActiveSheet.PivotTables(“PivotTable1”)
.PivotFields(“Nebenkontierung_1”).PivotItems(“(blank)”).Visible = True
.RefreshTable
.PivotFields(“Nebenkontierung_1”).PivotItems(“(blank)”).Visible = False
End With
End Sub

For Blank Cell:
Sub DeleteBlankRows()
With ActiveSheet.PivotTables(“PivotTable1”)
.PivotFields(“Nebenkontierung_1”).PivotItems(“”).Visible = True
.RefreshTable
.PivotFields(“Nebenkontierung_1”).PivotItems(“”).Visible = False
End With
End Sub

All types of suggestion and help will be highly appreciated

Thanks

[supermanPunch (Arpan)]

Hi @Tarif_Mohammad ,

As you have already tried with Filter Datatable Activity and Couldn’t Get the Expected Output, We can Give a Try on Linq Operations.

1. Assuming that you already read the excel sheet using Read Range Activity and Get the Output as Datatable. We can filter the Datatable to Keep only the Rows needed as per your Requirement .

Let’s Say we have Datatable variable, DT, Then we can use the Below Linq Expression in an Assign Activity:

rowArray = DT.AsEnumerable.Where(Function(x)Not(String.IsNullOrWhiteSpace(x("Nebenkontierung_1").ToString))).ToArray

where rowArray is of the Type Array of DataRow.

Next, we can Check if there are any values in rowArray with an if Activity with the Below Condition :

rowArray.Any

In the Then Part, we can use the Below Expression :

DT = rowArray.CopyToDatatable

In the Else Part, we can use the Below Expression :

DT = DT.Clone

[Tarif_Mohammad (Parth)]

@supermanPunch hi thanks for the fast response willtry it out and let u know