MERGE ROW CELLS BY MACRO

[K]

Hi i have this macro (please see below)

Sub InsertIt()
Dim LastRow As Long
Dim StartRow As Long
StartRow = Cells(Rows.Count, 1).End(xlUp).Row - 1
Cells(StartRow + 1, 1).Resize(6, 1).EntireRow.Insert
Cells(StartRow + 1, 9).Resize(6, 6).Merge
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False
With Range(Cells(StartRow, 1), Cells(LastRow, 1))
..DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
End With
Application.ScreenUpdating = True

End Sub

This macro basically insert 6 rows and i wanted to merge each row
cells (the one been inserted
by macro) from coloumn "I" to "N". one of online friend send me this
line to insert in macro to merge cells.
Cells(StartRow + 1, 9).Resize(6, 6).Merge
by putting this line in macro works fine but instead of merging each
row from coloumn "I" to "N" it merge 6 rows and make one big cell. i
want those rows (the one been inserted by macro) to merge each
saperatly from coloumn "I" to "N". Please any body can help as i need
this for my project. Thanks.

 

[Sandy Mann]

Please do not start a new thread every time that you ask a related
question - this just fragments your requests and makes it more difficult for
people to help you. This is the third thread that you have started on the
same theme

If you mean to put the merged cells in the inserted rows then try:

Sub InsertIt()
Dim LastRow As Long
Dim StartRow As Long
StartRow = Cells(Rows.Count, 1).End(xlUp).Row - 1

Cells(StartRow + 1, 1).Resize(6, 1).EntireRow.Insert

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
With Range(Cells(StartRow, 1), Cells(LastRow, 1))
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
End With

For mrow = StartRow + 2 To LastRow
Cells(mrow, 9).Resize(1, 6).Merge
Next mrow

Application.ScreenUpdating = True

End Sub

I note that you are setting StartRow to one Row less than the last Row then
adding 1 to it. Why not just use StartRow in the first place?



--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[K]

Please do not start a new thread every time that you ask a related
question - this just fragments your requests and makes it more difficult for
people to help you.  This is the third thread that you have started on the
same theme

If you mean to put the merged cells in the inserted rows then try:

Sub InsertIt()
Dim LastRow As Long
Dim StartRow As Long
StartRow = Cells(Rows.Count, 1).End(xlUp).Row - 1

Cells(StartRow + 1, 1).Resize(6, 1).EntireRow.Insert

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
With Range(Cells(StartRow, 1), Cells(LastRow, 1))
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
        Step:=1, Trend:=False
End With

For mrow = StartRow + 2 To LastRow
    Cells(mrow, 9).Resize(1, 6).Merge
Next mrow

Application.ScreenUpdating = True

End Sub

I note that you are setting StartRow to one Row less than the last Row then
adding 1 to it.  Why not just use StartRow in the first place?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk









- Show quoted text -

Hi Sandy, sorry for raising question again as i needed it urgent and i
get answer sometime very late . your macro working perfectly fine.
Thanks a lot