Method to remove empty rows from DataTable?
You can't modify the collection while you're enumerating it with For Each
, but a simple For
loop will work. You'll need to loop backwards to avoid skipping the row after a removed row.
You've also got your tests the wrong way round; if Item(0)
returns Nothing
, then Item(0).ToString
will throw a NullReferenceException
.
I'm assuming the dt.Rows.Item(0)
is a typo, and should read row.Item(0)
instead.
For i As Integer = dt.Rows.Count - 1 To 0 Step -1
Dim row As DataRow = dt.Rows(i)
If row.Item(0) Is Nothing Then
dt.Rows.Remove(row)
ElseIf row.Item(0).ToString = "" Then
dt.Rows.Remove(row)
End If
Next
Admin
Updated on June 14, 2022Comments
-
Admin almost 2 years
I parsed through an Excel spreadsheet and returned the entire result as a DataTable. However, this Excel spreadsheet has several empty rows that I would like to eliminate from the resulting DataTable. In particular, each empty row begins with an empty cell. So, I know that the entire row is empty if the value of the cell at the first index is empty. Note that I cannot simply modify the Excel spreadsheet because I have to work with exactly what the client has sent to me. Based on this information, I assumed that I could perform the following function to remove empty rows:
' DataTable dt has already been populated with the data For Each row As DataRow In dt.Rows If dt.Rows.Item(0).ToString = "" Then dt.Rows.Remove(row) ElseIf dt.Rows.Item(0) Is Nothing Then dt.Rows.Remove(row) End If Next
However, after crafting this solution, I am greeted with the following error:
Collection was modified; enumeration operation might not execute.
I now realize that I cannot alter the collection as I access it. How can I get around this? I'm wondering if I should create a new DataTable with the rows that aren't empty. Is this the best approach or are there better options?
EDIT: I have also tried iterating over the rows backwards:
For i = dt.Rows.Count() - 1 To 0 If dt.Rows.Item(i).Item(0).ToString = "" Then dt.Rows.RemoveAt(i) End If Next
-
Admin over 11 yearsThank you! This was exactly what I was looking for.