run-time error 1004 : Application-defined or object-defined error

[manvit]

Hi Excel folks,

I m trying to move every second row at the end of previous row.
This below code is being applied from the third row. I m getting the desired out , but i m also getting the below error.
" run-time error 1004 : Application-defined or object-defined error".

I m aware that error is in the below red line, but why is that an error
Can anyone help me out with where is the error in this code?

sub merging()
Dim sh As Worksheet, lr As Long, lc As Long
Set sh = Sheet4 'Edit sheet name.
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 1 Step -2
With sh
.Range(.Cells(i, 1), .Cells(i, Columns.Count).End(xlToLeft)).Copy .Cells(i - 1, Columns.Count).End(xlToLeft).Offset(0, 1)
.Rows(i).Delete
End With
Next
End Sub

any help will be very appreciated

Best,
Manvit

 

[Michael M]

Maybe this

Sub merging()
Dim sh As Worksheet, lr As Long, lc As Long
Set sh = Sheet4 'Edit sheet name.
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 1 Step -2
    lc = ActiveSheet.Cells(i, Columns.Count).End(xlToLeft).Column
        With sh
            .Range(.Cells(i, 1), .Cells(i, lc)).Copy Cells(i - 1, lc + 1)
            .Rows(i).Delete
        End With
Next i
End Sub

 

[manvit]

Maybe this

Sub merging()
Dim sh As Worksheet, lr As Long, lc As Long
Set sh = Sheet4 'Edit sheet name.
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For i = lr To 1 Step -2
    lc = ActiveSheet.Cells(i, Columns.Count).End(xlToLeft).Column
        With sh
            .Range(.Cells(i, 1), .Cells(i, lc)).Copy Cells(i - 1, lc + 1)
            .Rows(i).Delete
        End With
Next i
End Sub

Thanks for trying Michael but this too is giving me a same error!

 

[Michael M]

Do you have an odd number of rows ??
It will error if ste-2 can't find a row to work with !!

 

[Michael M]

Change

For i = lr To 1 Step -2

to

For i = lr To 2 Step -2

 

[manvit]

Do you have an odd number of rows ??
It will error if ste-2 can't find a row to work with !!

Yes Michael!, the sheet has data till 49th row.

 

[Michael M]

See post #5

 

[manvit]

Change

For i = lr To 1 Step -2

to

For i = lr To 2 Step -2

Thanks a lot Michael! it works. Can you please explain what was my mistake ?

 

[Michael M]

As you were trying to move data to the previous row, when it got to row 1, there was nowhere for the data to be moved to....so an error occured.
When the loop only goes to row 2, it finishes correctly.

 

[manvit]

Thank you,