Paul Edward Pagente Paul Edward Pagente - 2 months ago 23
Vb.net Question

Remove rows in Datagridview based on criteria

Good Morning
I have a program that looks like this.

enter image description here

My goal here is to remove rows where column

Tag
has a data of
Exclude
and this my code.

For Each row As DataGridViewRow In DataGridView1.Rows

If (row.Cells(4).Value.ToString = "Exclude") Then

DataGridView1.Rows.Remove(row)

End If

Next


Supposed to be I will add this code after the importing of data from excel to datagridview but its not working. I tried to put this on a button to see if it is working and it is working but then I need to click few times before all
Exclude
will gone.

Any wrong or changes in my code? If you want here is my code of importing to.

Dim conn As OleDbConnection
Dim dta As OleDbDataAdapter
Dim dts As DataSet
Dim excel As String
Dim OpenFileDialog As New OpenFileDialog
OpenFileDialog.InitialDirectory = My.Computer.FileSystem.SpecialDirectories.MyDocuments
OpenFileDialog.Filter = "All Files (*.*)|*.*|Excel files (*.xlsx)|*.xlsx|CSV Files (*.csv)|*.csv|XLS Files (*.xls)|*xls"
If (OpenFileDialog.ShowDialog(Me) = System.Windows.Forms.DialogResult.OK) Then
Dim fi As New FileInfo(OpenFileDialog.FileName)
Dim FileName As String = OpenFileDialog.FileName
excel = fi.FullName
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excel + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1';")
dta = New OleDbDataAdapter("Select F3 as ItemCode,F4 as Description,F6 as OrderedQty,F9 as Remarks,F10 as Tag From [Official Transmittal Form$]", conn)
dts = New DataSet
dta.Fill(dts, 13, 768, "MyTable")
DataGridView1.DataSource = dts
DataGridView1.DataMember = "MyTable"
conn.Close()

'This is the Delete Code below.
For Each row As DataGridViewRow In DataGridView1.Rows
If (row.Cells(4).Value.ToString = "Exclude") Then
DataGridView1.Rows.Remove(row)
End If
Next
End If


TYSM for future help

Answer

You can replace your ForEach loop with this one.

The problem with your code is you cannot loop the rows of the datagrid while removing some of its rows at the same time.

Try this instead:

        Dim resultSet = From drow As DataGridViewRow In DataGridView1.Rows _
                Where drow.Cells(4).Value.ToString.ToUpper = "EXCLUDE" _
                Select drow.Index
        For Each rowIndex In resultSet
            DataGridView1.Rows.RemoveAt(rowIndex)
        Next