Insert New row into Excel Using VB

[jvdub22]

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.

[RobDog888]

Option Explicit
'Add reference to MS Excel xx.0 Object Library
Private moApp As Excel.Application
 
Private Sub Command1_Click()
    Dim oWB As Excel.Workbook
    
    moApp.Visible = True
    Set oWB = moApp.Workbooks.Open("D:\My Documents\Book1.xls")
    oWB.Sheets("Sheet1").Rows("2:2").Select
    Selection.Insert Shift:=xlDown 'Insert a row at the selection (row 2) and shift all rows below down 1
    
End Sub
 
Private Sub Form_Load()
    Set moApp = New Excel.Application
End Sub

[jvdub22]

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?

[RobDog888]

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.

Option Explicit
'Add reference to MS Excel xx.0 Object Library
Private moApp As Excel.Application
 
Private Sub Command1_Click()
    
    Dim oWB As Excel.Workbook
    Dim lMaxRowNumber As Long
    
    moApp.Visible = True
    Set oWB = moApp.Workbooks.Open("D:\My Documents\Book1.xls")
    'Add first new row at the very end
    lMaxRowNumber = oWB.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
    oWB.Sheets("Sheet1").Rows(CStr(lMaxRowNumber + 1) & ":" & CStr(lMaxRowNumber + 1)).Select
    Application.Selection.Insert Shift:=xlDown 'Insert a row at the selection (last row + 1)
    oWB.Sheets("Sheet1").Cells(lMaxRowNumber + 1, 1).Value = "Added new row"
    'Add another new row at the new very end
    lMaxRowNumber = oWB.Sheets("Sheet1").Cells.SpecialCells(xlCellTypeLastCell).Row
    oWB.Sheets("Sheet1").Rows(CStr(lMaxRowNumber + 1) & ":" & CStr(lMaxRowNumber + 1)).Select
    Application.Selection.Insert Shift:=xlDown
    oWB.Sheets("Sheet1").Cells(lMaxRowNumber + 1, 1).Value = "Added second new row"
    
End Sub
 
Private Sub Form_Load()
    Set moApp = New Excel.Application
End Sub

[jvdub22]

When i use:

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:

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.

[RobDog888]

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.

Private Sub Workbook_Open()
    MsgBox "Last used row in column D: " & ActiveSheet.Range("D:D").SpecialCells(xlCellTypeBlanks).Row - 1
End Sub

[vexx5555]

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

[IHappenToBe]

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:

Spreadsheet1.Range("5:5").Insert

That's it.

Or, if you want to do it with a variable:

Spreadsheet1.Range(Trim(Str(n)) + ":" + Trim(Str(n))).Insert
' meaning:                           5       :                 5