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

manvit

New Member
Joined
Oct 31, 2018
Messages
19
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
 
Last edited:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,331
Office Version
2013
Platform
Windows
Maybe 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
    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
 
Last edited:

manvit

New Member
Joined
Oct 31, 2018
Messages
19
Maybe 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
    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

Well-known Member
Joined
Oct 27, 2005
Messages
18,331
Office Version
2013
Platform
Windows
Do you have an odd number of rows ??
It will error if ste-2 can't find a row to work with !!
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,331
Office Version
2013
Platform
Windows
Change

For i = lr To 1 Step -2

to

For i = lr To 2 Step -2
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,331
Office Version
2013
Platform
Windows
See post #5
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,331
Office Version
2013
Platform
Windows
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.
 

Forum statistics

Threads
1,086,246
Messages
5,388,672
Members
402,134
Latest member
McKnze21

Some videos you may like

This Week's Hot Topics

Top