Results 1 to 8 of 8

Thread: Insert New row into Excel Using VB

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2004
    Posts
    14

    Insert New row into Excel Using VB

    I have a spreadsheet with columns full of values that are sorted ascending. In my vb program I have recordsets with values that need to be inserted into the spreadsheet columns in order. How do I search through the columns in excel and insert my recordset values in the appropriate position.

    for example: my record set: 2, 4 , 8, 9

    column A: 1, 3, 7, 15

    I need to insert my record set into column A so the it looks like:

    column A: 1, 2, 3, 4, 7, 8, 9, 15

    Note: I need to do this by adding entire rows and not just shifting cells.

    Im not familiar with using VB to navigate through excel worksheets and getting values, any help is appreciated.

  2. #2
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    VB Code:
    1. Option Explicit
    2. 'Add reference to MS Excel xx.0 Object Library
    3. Private moApp As Excel.Application
    4.  
    5. Private Sub Command1_Click()
    6.     Dim oWB As Excel.Workbook
    7.    
    8.     moApp.Visible = True
    9.     Set oWB = moApp.Workbooks.Open("D:\My Documents\Book1.xls")
    10.     oWB.Sheets("Sheet1").Rows("2:2").Select
    11.     Selection.Insert Shift:=xlDown 'Insert a row at the selection (row 2) and shift all rows below down 1
    12.    
    13. End Sub
    14.  
    15. Private Sub Form_Load()
    16.     Set moApp = New Excel.Application
    17. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  3. #3

    Thread Starter
    New Member
    Join Date
    Nov 2004
    Posts
    14
    How do I start at the first value in the column and compare it the value in my recordset and continue moving down the column until i find where to insert. Also how do i know when there are no more values in the column to compare and to place the value at the end of the column?

  4. #4
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710
    To read each cell you will need to loop through the range and
    compare each cell's value with your rs' field value.
    You can use the .Rows collection and iterate through the first row
    using one loop and then iterate through an second nested inner
    loop for the columns. .Rows(r,c) in numbers.

    VB Code:
    1. Option Explicit
    2. 'Add reference to MS Excel xx.0 Object Library
    3. Private moApp As Excel.Application
    4.  
    5. Private Sub Command1_Click()
    6.    
    7.     Dim oWB As Excel.Workbook
    8.     Dim lMaxRowNumber As Long
    9.    
    10.     moApp.Visible = True
    11.     Set oWB = moApp.Workbooks.Open("D:\My Documents\Book1.xls")
    12.     'Add first new row at the very end
    13.     lMaxRowNumber = oWB.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
    14.     oWB.Sheets("Sheet1").Rows(CStr(lMaxRowNumber + 1) & ":" & CStr(lMaxRowNumber + 1)).Select
    15.     Application.Selection.Insert Shift:=xlDown 'Insert a row at the selection (last row + 1)
    16.     oWB.Sheets("Sheet1").Cells(lMaxRowNumber + 1, 1).Value = "Added new row"
    17.     'Add another new row at the new very end
    18.     lMaxRowNumber = oWB.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
    19.     oWB.Sheets("Sheet1").Rows(CStr(lMaxRowNumber + 1) & ":" & CStr(lMaxRowNumber + 1)).Select
    20.     Application.Selection.Insert Shift:=xlDown
    21.     oWB.Sheets("Sheet1").Cells(lMaxRowNumber + 1, 1).Value = "Added second new row"
    22.    
    23. End Sub
    24.  
    25. Private Sub Form_Load()
    26.     Set moApp = New Excel.Application
    27. End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  5. #5

    Thread Starter
    New Member
    Join Date
    Nov 2004
    Posts
    14

    Re: Insert New row into Excel Using VB

    When i use:

    Code:
    lMaxRowNumber = oWB.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
    i get back the value of the last row on the entire sheet. how can i get the last row in a particular column?

    Ive tried to set a range object:

    Code:
    Dim rng As range 
    Set rng = Range("D:D")
    lMaxRowNumber = rng.SpecialCells(xlCellTypeLastCell).row
    but this still returns the last row of the entire sheet.

  6. #6
    Ex-Super Mod RobDog888's Avatar
    Join Date
    Apr 2001
    Location
    LA, Calif. Raiders #1 AKA:Gangsta Yoda™
    Posts
    60,710

    Re: Insert New row into Excel Using VB

    It doesnt quite work that way. You need to create a range for the column.
    Then search for the first blank cell. Assuming that your range is contigous.
    xlCellTypeLastCell is only for detecting the last used row in the sheet for all
    columns combined.

    This should help.

    Code:
    Private Sub Workbook_Open()
        MsgBox "Last used row in column D: " & ActiveSheet.Range("D:D").SpecialCells(xlCellTypeBlanks).Row - 1
    End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer coding questions via PM. Please post a thread in the appropriate forum.

    Microsoft MVP 2006-2011
    Office Development FAQ (C#, VB.NET, VB 6, VBA)
    Senior Jedi Software Engineer MCP (VB 6 & .NET), BSEE, CET
    If a post has helped you then Please Rate it!
    Reps & Rating PostsVS.NET on Vista Multiple .NET Framework Versions Office Primary Interop AssembliesVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6VB.NET Attributes Ex.Outlook Global Address ListAPI Viewer utility.NET API Viewer Utility
    System: Intel i7 6850K, Geforce GTX1060, Samsung M.2 1 TB & SATA 500 GB, 32 GBs DDR4 3300 Quad Channel RAM, 2 Viewsonic 24" LCDs, Windows 10, Office 2016, VS 2019, VB6 SP6

  7. #7
    New Member
    Join Date
    Jul 2011
    Posts
    1

    Re: Insert New row into Excel Using VB

    Hi!

    I have problems with inserting new row in my program.

    Here is the code:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    Dim NewRow As Double
    NewRow = workbook.Worksheets("sheet1").Range("A1").Value + 1


    worksheet.Cells(NewRow, 1).Value = Label4.Text

    worksheet.Cells(NewRow, 2).Value = Label5.Text

    worksheet.Cells(NewRow, 3).Value = Label6.Text

    worksheet.Cells(NewRow, 4).Value = Label7.Text

    worksheet.Cells(NewRow, 5).Value = Label8.Text

    worksheet.Cells(NewRow, 6).Value = Label9.Text

    worksheet.Cells(NewRow, 7).Value = Label10.Text

    worksheet.Cells(NewRow, 8).Value = Label11.Text

    worksheet.Cells(NewRow, 9).Value = CheckBox1.Text

    worksheet.Cells(NewRow, 10).Value = CheckBox2.Text

    worksheet.Cells(NewRow, 11).Value = CheckBox3.Text

    worksheet.Cells(NewRow, 12).Value = CheckBox4.Text

    worksheet.Cells(NewRow, 13).Value = Label1.Text

    worksheet.Cells(NewRow, 14).Value = Label2.Text



    worksheet.Cells(NewRow, 1).Value = TextBox1.Text

    worksheet.Cells(NewRow, 2).Value = TextBox2.Text

    worksheet.Cells(NewRow, 3).Value = ComboBox1.Text

    worksheet.Cells(NewRow, 4).Value = TextBox3.Text

    worksheet.Cells(NewRow, 5).Value = TextBox4.Text

    worksheet.Cells(NewRow, 6).Value = TextBox6.Text

    worksheet.Cells(NewRow, 7).Value = DateTimePicker1.Text

    worksheet.Cells(NewRow, 8).Value = TextBox5.Text

    worksheet.Cells(NewRow, 9).Value = CheckBox1.Checked

    worksheet.Cells(NewRow, 10).Value = CheckBox2.Checked

    worksheet.Cells(NewRow, 11).Value = CheckBox3.Checked

    worksheet.Cells(NewRow, 12).Value = CheckBox4.Checked

    worksheet.Cells(NewRow, 13).Value = RichTextBox1.Text

    worksheet.Cells(NewRow, 14).Value = RichTextBox2.Text



    End Sub

  8. #8
    Member
    Join Date
    Jun 2010
    Posts
    40

    Re: Insert New row into Excel Using VB

    Quote Originally Posted by jvdub22 View Post
    Im not familiar with using VB to navigate through excel worksheets and getting values, any help is appreciated.
    I don't understand why people need to give so complicated answers.

    Let's say you need to insert a row between rows 4 and 5:

    Code:
    Spreadsheet1.Range("5:5").Insert
    That's it.

    Or, if you want to do it with a variable:
    Code:
    Spreadsheet1.Range(Trim(Str(n)) + ":" + Trim(Str(n))).Insert
    ' meaning:                           5       :                 5

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width