The thing is taht im automating things...
in such a case i wont know how many rows will be there after the 3 iserted
rows
taht is for each name in column A , before merging all similar names .. i
insert the 3 rows and then merge it. so after each name i will have these 3
rows t be inserted automatically

"Joel" wrote:

Are you suppose to have data in row 65536?
Press Shift-CNTL and then down arrow to get to last Row.

When you insert a row and havve data at the last row it will give this error
message. You can copy the data into existing rows, or get rid of the data in
row 65536.
"dee" wrote:

i tried but it throws me the following error :
run time error 1004. to prevent loss of data, cannot shift non blank cells
off the worksheet.
how can i rectify this problem?




"Joel" wrote:

If you are copying a row you need to paste it into a row number not a cell

For i1 = 3 To e
If (ps < Range("C" & i1)) Then

'' 'insertion 3 rows
Sheets("Sheet2").Rows("1:3").copy
Sheets("FTP").Rows(i1).insert
ActiveWindow.ActivateNext

'' 'end of insertion of three rows

Range("B" & st & ":B" & i1 - 1).merge
Range("A" & st & ":A" & i1 - 1).merge
Range("C" & st & ":C" & i1 - 1).merge

ps = Range("C" & i1)
st = i1
ElseIf (ps = Range("C" & i1) And i1 < 3) Then
Range("A" & i1) = ""
Range("B" & i1) = ""
Range("C" & i1) = ""
'Range("D" & i1) = ""


End If


Next i1

"dee" wrote:

hi,
I have the following code which merges cells... but before merging, i am
taking 3 rows from sheet2 , copying it and putting it into my FTP sheet
. but it throws error.. can any1 help me in the section "inserting 3 rows"..
merging is happening fine.. all i need is taht the 3 rows should be added
dynamically before merging... pls help in rectifying


i have given portion of teh entire code here

For i1 = 3 To e
If (ps < Range("C" & i1)) Then

'' 'insertion 3 rows
Sheets("Sheet2").Select
Rows("1:3").Select
Selection.Copy
Sheets("FTP").Select
Range("E" & i1).Select
Selection.Insert Shift:=xlDown
ActiveWindow.ActivateNext

'' 'end of insertion of three rows

Range("B" & st & ":B" & i1 - 1).merge
Range("A" & st & ":A" & i1 - 1).merge
Range("C" & st & ":C" & i1 - 1).merge

ps = Range("C" & i1)
st = i1
ElseIf (ps = Range("C" & i1) And i1 < 3) Then
Range("A" & i1) = ""
Range("B" & i1) = ""
Range("C" & i1) = ""
'Range("D" & i1) = ""


End If


Next i1