Insert Row in Excel and Merge Cells (VS2005)

[danasegarane]

Hi All,
I am using this code to insert one row and trying to merge cells (G21:I31).

Private Sub InsertRow(ByVal sheet As Excel.Worksheet, ByVal rowcount As Integer)
            Dim range As Excel.Range
            Dim row As Excel.Range
            Dim remark As Excel.Range
            Dim newRow As Excel.Range
            Dim _newrow As Integer = rowcount - _TRIPREPORTROWS
            If rowcount > _TRIPREPORTROWS Then
                'Select the Last DataRow
                range = sheet.Range(_RowRange & _RowIndex)
                row = range.EntireRow
                For index As Integer = 1 To _newrow 'Insert N new Rows
                    row.Insert(xlShiftDown) 'Insert one Row
                    newRow = sheet.Range(_RowRange & _RowIndex) 'Get the New Row
                    remark = newRow.EntireRow.Range("G32:I32") 'Get the new Row Range
                    remark.Cells.Merge() 'Merge

                Next
            End If

        End Sub

It is merging G54:I54 .. Any ideas ?

[westconn1]

i guess remark is a range relative to newrow range, rather than to sheet range

[danasegarane]

Is there any method available to merge here it self ?

newRow = sheet.Range(_RowRange & _RowIndex) 'Get the New Row

[westconn1]

try
newrow.range("g1:i1).mergecells

[danasegarane]

That does not work well.
I managed with this one.

Private Sub InsertRow(ByVal sheet As Excel.Worksheet, ByVal rowcount As Integer)
            Dim range As Excel.Range
            Dim row As Excel.Range
            Dim rowindex As Integer = 29
            Dim _newrow As Integer = rowcount - _TRIPREPORTROWS
            If rowcount > _TRIPREPORTROWS Then
                'Select the Last DataRow
                range = sheet.Range(_RowRange & _RowIndex)
                row = range.EntireRow
                For index As Integer = 1 To _newrow 'Insert N new Rows
                    row.Insert(xlShiftDown) 'Insert one Row
                    sheet.Range(String.Format("G{0}:I{0}", rowindex)).Merge()
                    'Increase row index
                    rowindex += 1
                Next
            End If
 
        End Sub

But I am not sure is this the correct method

[Siddharth Rout]

Hi Dana

What is the value of _TRIPREPORTROWS, _RowRange and _RowIndex?

[anhn]

Hard to write it shorter:

Dim aRange As Excel.Range
Dim r  As Integer 
Dim N  As Integer: N = 12  ' = rowcount - _TRIPREPORTROWS
 
With sheet
    '-- don't know what is your _RowRange & _RowIndex, "B10" is just a sample
    r = .Range("B10").Row ' = .Range(_RowRange & _RowIndex).Row
    '-- insert N rows above row r
    .Rows(r).Resize(N).Insert
    '-- the first inserted row now becomes row r
    '-- merge columns G:I on each new inserted row
    For Each aRange In .Rows(r).Resize(N).Columns("G:I").Rows
        aRange.Merge
    Next
End With