Good day guys I need help for adding rows in datagrid where the rows to be added
are equal to the number of rows copied from excel. I can copy the data but it displays
only the first row from excel on the last row of datagrid but if I add let's just say 4
rows in datagrid 4 rows are copied from excel. What I want is to add rows equal
to the rows copied from excel to datagrid. If possible I f it can add another row
just like when you put data in a row it will automatically add one below the row you
are putting the data

 Private Sub Button3_Click(sender As Object, e As EventArgs) Handles Button3.Click
        Dim s As String

            s = Clipboard.GetText()
            Dim i, ii As Integer

            Dim tArr() As String = s.Split(ControlChars.NewLine)
            Dim arT() As String
            Dim cc, iRow, iCol As Integer

            iRow = DataGridView1.SelectedCells(0).RowIndex
            iCol = DataGridView1.SelectedCells(0).ColumnIndex
            For i = 0 To tArr.Length - 1
                If tArr(i) <> "" Then
                    arT = tArr(i).Split(vbTab)
                    cc = iCol
                    For ii = 0 To arT.Length - 1
                        If cc > DataGridView1.ColumnCount - 1 Then Exit For
                        If iRow > DataGridView1.Rows.Count - 1 Then Exit Sub
                        With DataGridView1.Item(cc, iRow)
                            .Value = arT(ii).TrimStart

                        End With
                        cc = cc + 1
                    iRow = iRow + 1
                End If


        Catch ex As Exception
            MsgBox("Please redo Copy and Click on cell")
        End Try
    End Sub


Edited by jared.geli: Edit

4 Years
Discussion Span
Last Post by richardsonka
Featured Replies

There exists a Rows.Add method.
You could do it like this:
DataGridView1.Rows.Add(arT);(arT, your string array from line 17)
Look here for another example.

Edited by ddanbe: addition


Well just after line 17.
Forget all the for-loop mumbo jumbo, that follows, remove it.
The Rows Add method, takes care of all the creepy details.


No problem if you are confused, we are here, trying to help you out.
This is how I see it:

    s = Clipboard.GetText()
    Dim i, ii As Integer
    Dim tArr() As String = s.Split(ControlChars.NewLine)
    Dim arT() As String

    For i = 0 To tArr.Length - 1
        If tArr(i) <> "" Then
            arT = tArr(i).Split(vbTab)
        End If
Catch ex As Exception
    MsgBox("Please redo Copy and Click on cell")
End Try

Hope this clears things up, if not, just ask.


there's an error it says:Rows cannot be programmatically added to the DataGridView's rows collection when the control is data-bound.

My datagridview shows the data from my db:

Here's the code how I connect my db to datagrid:

 Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ace.oledb.12.0;data source=F:\Office\VehicleAltered.accdb")
        Dim cmd As OleDbCommand = New OleDbCommand("SELECT EmpID, Driver as [Name] FROM Data4", con)
        adpt = New OleDbDataAdapter(cmd)
        'Here one CommandBuilder object is required.
        'It will automatically generate DeleteCommand,UpdateCommand and InsertCommand for DataAdapter object  
        Dim builder As OleDbCommandBuilder = New OleDbCommandBuilder(adpt)
        DtSet = New DataSet()
        adpt.Fill(DtSet, "Data4")
        DataGridView1.DataSource = DtSet.Tables("Data4").DefaultView
        con = Nothing

So, as I understand you well, you have a Datagridview connected with a database and you want to do some copy/paste from an external excel sheet to the datagridview?


According to MSDN there is a Rows property.
But sorry if I gave some bad info, I find it a bit complicated myself and it is some time ago I used a DataTable.
You must first use the NewRow method before you can Add the row.


It's ok. How about this one

 Dim rowSplitter As Char() = {vbCr, vbLf}
        Dim columnSplitter As Char() = {vbTab}

        'get the text from clipboard

        Dim dataInClipboard As IDataObject = Clipboard.GetDataObject()
        Dim stringInClipboard As String = CStr(dataInClipboard.GetData(DataFormats.Text))

        'split it into lines
        Dim rowsInClipboard As String() = stringInClipboard.Split(rowSplitter, StringSplitOptions.RemoveEmptyEntries)

        'get the row and column of selected cell in grid
        Dim r As Integer = dgv.SelectedCells(0).RowIndex
        Dim c As Integer = dgv.SelectedCells(0).ColumnIndex

        'add rows into grid to fit clipboard lines
        If (dgv.Rows.Count < (r + rowsInClipboard.Length)) Then
            dgv.Rows.Add(r + rowsInClipboard.Length - dgv.Rows.Count)
        End If

        ' loop through the lines, split them into cells and place the values in the corresponding cell.
        Dim iRow As Integer = 0
        While iRow < rowsInClipboard.Length
            'split row into cell values
            Dim valuesInRow As String() = rowsInClipboard(iRow).Split(columnSplitter)
            'cycle through cell values
            Dim iCol As Integer = 0
            While iCol < valuesInRow.Length
                'assign cell value, only if it within columns of the grid
                If (dgv.ColumnCount - 1 >= c + iCol) Then
                    dgv.Rows(r + iRow).Cells(c + iCol).Value = valuesInRow(iCol)
                End If
                iCol += 1
            End While
            iRow += 1
        End While

I used that code first but it doesn't work.
dgv is DataGridView1


Hi ddanbe thanks for suggesting me aboy the DtSet.Tables(0).Rows.Add(). I figured out how I can do it but not exactly what I wanted. I created a keydown event in a textbox and loop the DtSet.Tables(0).Rows.Add() according to the number you inputed in the textbox. Thanks again


Up until recently, I have bound the datatable directly to the DataGridView; ,however I now impose a BindingSource into the mix as it has other benefits not relevant to this article. So, bind the data to the DataGridView.
private void btnGetData_Click(object sender, EventArgs e)

Edited by richardsonka: modify

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.