Delete all Empty or Blank Rows from DataTable

In this blog you will learn how to Delete all empty or blank rows from DataTable in vb.net (none of columns of that row holds any value).

If you want to delete all empty rows from your DataTable (none of columns of that row holds any value) then either you can do it in old fashion i.e. looping or using lamda expression as:

Dim dttt As DataTable = DtSet.Tables(0).Rows.Cast(Of DataRow)().Where(Function(row) Not row.ItemArray.All(Function(field) field Is DBNull.Value Or field.Equals(""))).CopyToDataTable()

If you don't want to copy into another DataTable then:

DtSet.Tables(0).AsEnumerable().Where(Function(row) row.ItemArray.All(Function(field) field Is Nothing Or field Is DBNull.Value Or field.Equals(""))).ToList().ForEach(Sub(row) row.Delete())

DtSet.Tables(0).AcceptChanges()

It had to be done using loop as:

For rowCounter As Integer = DtSet.Tables(0).Rows.Count - 1 To 0 Step -1
    Dim emptyColumnCount As Integer = 0
    Dim row As DataRow = DtSet.Tables(0).Rows(rowCounter)

    For Each rowItem In row.ItemArray()
        If rowItem Is Nothing Or rowItem Is DBNull.Value Or rowItem.Equals("") Then
            emptyColumnCount += 1
        End If
    Next

    If emptyColumnCount = DtSet.Tables(0).Columns.Count Then
        DtSet.Tables(0).Rows.Remove(row)
    End If
Next

You can also apply some conditional columns as:

'This code will not delete as it reads value as ""(empty string)
If DtSet.Tables(0).Rows(rowCounter)("columnNameOrColumnIndex) Is DBNull.Value Then
    DtSet.Tables(0).Rows(R).Delete()
End If


'This will work & delete all rows based on a columns value -
If DtSet.Tables(0).Rows(rowCounter)(columnNameOrColumnIndex) Is DBNull.Value OR String.IsNullOrEmpty(DtSet.Tables(0).Rows(rowCounter)(columnNameOrColumnIndex)) Then
     DtSet.Tables(0).Rows(rowCounter).Delete()
End If

Happy VB.Net coding!