remove blank cells from datatable

Question

I have following datatable:

enter image description here

As shown in pic, there are some blank cells in this data table.

I want to remove them and show only the cells with values.

I tried following code but it didnt worked:

For i=0 to dt.Rows.count-1

For j=0 to dt.Column.count-1


if dt.rows(i).tostring()="" then

dt.rows.remove(i)

end if

next

Next

This code does not works. Please help me.

Answer in c# can also help me.

where this blank cell come from?? how do you fill your table??? — AminM, Aug 17 '13 at 13:50
here is your answer http://stackoverflow.com/questions/1766902/remove-all-columns-with-no-data-from-datatable — Ehsan, Aug 17 '13 at 14:00

dotNET · Accepted Answer · 2013-08-17T14:07:42.783

Ideally you only want to remove a row when ALL of its cells are empty. The algo you have shown will delete the row even if a single cell in it is empty. You should change your code like this:

First remove empty rows:

For i = 0 To dt.Rows.Count - 1
    flag = True

    For j = 0 To dt.Columns.Count - 1
        If Not(dt.Rows(i).IsNull(j)) AndAlso dt.Rows(i)(j).ToString() <> "" Then
            flag = False
            Exit For
        End If
    Next

    If flag Then dt.Rows(i).Delete()
Next

dt.AcceptChanges()

Now remove empty columns:

For i = 0 To dt.Columns.Count - 1
    flag = True

    For j = 0 To dt.Rows.Count - 1
        If Not(dt.Rows(j).IsNull(i)) AndAlso dt.Rows(j)(i).ToString() <> "" Then
            flag = False
            Exit For
        End If
    Next

    If flag Then dt.Columns.RemoveAt(i)
Next

dt.AcceptChanges()

this deletes the empty rows on top, but not the columns. – Farhan Mukadam Aug 17 '13 at 14:02 — Farhan Mukadam, Aug 17 '13 at 14:02

score 1 · Answer 2 · answered Aug 17 '13 at 14:23

You had better remove the columns first:

dt.Columns.OfType<DataColumn>()
          .Where(c=>!dt.Rows.OfType<DataRow>()
                            .Any(r=>r.Field<string>(c.ColumnName) != ""))
          .ToList()
          .ForEach(c=>dt.Columns.Remove(c));

//Remove rows
dt.Rows.OfType<DataRow>()
       .Where(r=>!dt.Columns.OfType<DataColumn>()
                            .Any(c=>r.Field<string>(c.ColumnName) != ""))
       .ToList()
       .ForEach(r=>dt.Rows.Remove(r));

remove blank cells from datatable

2 Answers2