Insert Row in Excel and Merge Cells (VS2005)
Hi All,
I am using this code to insert one row and trying to merge cells (G21:I31).
Code:
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 ?
Re: Insert Row in Excel and Merge Cells (VS2005)
i guess remark is a range relative to newrow range, rather than to sheet range
Re: Insert Row in Excel and Merge Cells (VS2005)
Is there any method available to merge here it self ?
Code:
newRow = sheet.Range(_RowRange & _RowIndex) 'Get the New Row
Re: Insert Row in Excel and Merge Cells (VS2005)
try
newrow.range("g1:i1).mergecells
Re: Insert Row in Excel and Merge Cells (VS2005)
That does not work well.
I managed with this one.
vb Code:
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
Re: Insert Row in Excel and Merge Cells (VS2005)
Hi Dana
What is the value of _TRIPREPORTROWS, _RowRange and _RowIndex?
Re: Insert Row in Excel and Merge Cells (VS2005)
Hard to write it shorter:
vb Code:
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