Delete all Empty or Blank Rows from DataTable

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:

  1. 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:
  1. 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())  
  2.   
  3. DtSet.Tables(0).AcceptChanges()  
It had to be done using loop as:
  1. For rowCounter As Integer = DtSet.Tables(0).Rows.Count - 1 To 0 Step -1  
  2.     Dim emptyColumnCount As Integer = 0  
  3.     Dim row As DataRow = DtSet.Tables(0).Rows(rowCounter)  
  4.       
  5.     For Each rowItem In row.ItemArray()  
  6.         If rowItem Is Nothing Or rowItem Is DBNull.Value Or rowItem.Equals(""Then  
  7.             emptyColumnCount += 1  
  8.         End If  
  9.     Next  
  10.   
  11.     If emptyColumnCount = DtSet.Tables(0).Columns.Count Then  
  12.         DtSet.Tables(0).Rows.Remove(row)  
  13.     End If  
  14. Next  
You can also apply some conditional columns as:
  1. 'This code will not delete as it reads value as ""(empty string)    
  2. If DtSet.Tables(0).Rows(rowCounter)("columnNameOrColumnIndex) Is DBNull.Value Then       
  3.     DtSet.Tables(0).Rows(R).Delete()      
  4. End If     
  5.   
  6.   
  7. 'This will work & delete all rows based on a columns value -  
  8. If DtSet.Tables(0).Rows(rowCounter)(columnNameOrColumnIndex) Is DBNull.Value OR String.IsNullOrEmpty(DtSet.Tables(0).Rows(rowCounter)(columnNameOrColumnIndex)) Then  
  9.      DtSet.Tables(0).Rows(rowCounter).Delete()  
  10. End If  
Happy VB.Net coding!
Ebook Download
View all
Learn
View all