How to hide datagridview rows that contain null values in vb.net

Question

0.00/5 (No votes)

See more:

Hi I'm hoping to find a way to hide certain rows of my datagridview if they contain nothing or null. My datagridview is bound to a datasource that is an excel worksheet. I'm hoping on import that I can hide the rows that are empty and display only those that contain values. I'm thiking something like this will work but I've had no luck as of late.

VB

Dim Empty As Boolean = True

For i As Integer = 0 To dataGridView1.Rows.Count - 1
    Empty = True
    For j As Integer = 0 To dataGridView1.Columns.Count - 1
        If dataGridView1.Rows(i).Cells(j).Value IsNot Nothing AndAlso dataGridView1.Rows(i).Cells(j).Value.ToString() <> "" Then
            Empty = False
            Exit For
        End If
    Next
    If Empty Then
        dataGridView1.Rows.RemoveAt(i)
    End If
Next

If anyone can give any suggestions or help on the matter I would greatly appreciate it!

Posted 8-Aug-13 4:36am

CAS1224

Updated 15-Jun-17 18:20pm

v2

Add a Solution

3 solutions

Solution 1

I've never tried doing this, but I think this will suit your needs:

VB

Protected Sub TestGrid_RowDataBound(ByVal sender As Object, _
ByVal e As GridViewRowEventArgs)
    Dim DVR As DataRowView = CType(e.Row.DataItem, DataRowView)

    If e.Row.RowType = DataControlRowType.DataRow Then
        Dim Visible As Boolean = True
        For i As Integer = 0 To e.Row.Cells.Count - 1
            If DVR(i) Is Nothing Then
                Visible = False
                Exit For
            End If
        Next
        e.Row.Visible = Visible
    End If
End Sub

In the grid's RowDataBound event, first determine if you are binding a data row; we do not need to run this code on header rows, footer rows, etc.

Next, get a reference to the DataItem, for the row, casting it to DataRowView. This object has a default property, and you can reference the columns either by index or by column name.

Perform your checks: you can test every column using an integer loop, or just selected columns. The property returns an Object, so you may want to cast it to a specific data type to look for particular values (maybe you want to suppress the row if an integer column is less than zero, for example). If you use this with a data source from SQL, you would test against DBNull.Value to look for a SQL null.

If the row meets the specifications for "suppress this row", then simply set the row to be not visible. When the control gets rendered, any invisible rows will be omitted from the output.

Update I changed the sample code to be more in line with what you are asking for.

Posted 8-Aug-13 5:26am

Gregory Gadow

Updated 8-Aug-13 5:34am

v2

Comments

CAS1224 8-Aug-13 20:50pm

Thanks for your answer. My datagridview doesn't seem to have the RowDataBound event, is there an alternative?

CAS1224 9-Aug-13 9:11am

I'm using a datagridview not a dataview. There has to be an alternative of this to work with the datagridview.

Gregory Gadow 9-Aug-13 11:52am

D'oh! I've been so focused on web programming recently that I.... anyway. Let me post another answer for you with code.

Solution 4

To remove spesific value

VB

Dim rdval As Integer = Form1.DGVlap.RowCount - 1
    If rdval > 0 Then
       Try
            For i As Integer = 0 To rdval
               If i > 0 Then
                  If Form1.DGVlap.Rows(i).Cells(12).Value.ToString = "1" Then
                     Form1.DGVlap.Rows(i).Visible = False
                  End If
               End If
            Next
       Catch ex As Exception
            MsgBox(ex.Message)
       End Try
     End If

Get Error on Row 0 if value = 1, me remove row 0 with add - If i > 0 Then -

Posted 15-Jun-17 18:20pm

Member 11864556

Updated 15-Jun-17 18:28pm

v3

Comments

Richard Deeming 16-Jun-17 11:13am

This question was asked, answered, and solved FOUR YEARS AGO!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Gregory Gadow · Accepted Answer · 2013-08-09T06:10:00

Sorry about the other answer I gave: I have been so focused on web programming recently that I created a solution for the wrong control.

I have had very little cause to use databound grids in WinForms, but I believe this will do what you want:

VB

Private Sub TestGridView_DataBindingComplete(ByVal sender As Object, _
ByVal e As DataGridViewBindingCompleteEventArgs) _
Handles DataGridView1.DataBindingComplete

    For Each Row As DataGridViewRow In CType(sender, DataGridView).Rows
        Dim Visible As Boolean = True

        'Do this to inspect all cells in the row
        For i As Integer = 0 To Row.Cells.Count - 1
            If Row.Cells(i).Value Is Nothing Then
                Visible = False
                Exit For
            End If
        Next

        'Or you can check specific columns for their values
        If Row.Cells(0).Value Is Nothing OrElse _
        (IsNumeric(Row.Cells(0).Value) AndAlso CInt(Row.Cells(0).Value) < 0) Then
            Visible = False
        End If

        Row.Visible = Visible
    Next
End Sub

The DataBindingComplete event is fired after all of the data has been bound. Go through row by row and make a determination on whether or not the row should be visible or not.

The first section of code inspects every cell value in the row to determine whether or not the row should be visible.

The second one shows how to test specific columns for specific values; in this case, look only at the first column and suppress the row if it either nothing or else an integer with a negative value. The OrElse and AndAlso joiners short-circuit the evaluation process: if the value is Nothing then statement is true and the numeric tests never get done for the cell; if the value is not numeric then it will not try to convert it.

After setting (or not) the value of Visible, the row visibility is set and we move on to the next row, until every row has been tested. This is a very slow way of doing this, and there will be a noticeable lag if you have a lot of rows, but I don't see any other way.

Let me know if this does what you need. If so, don't forget to accept the solution :)