Solved: Error 1004 in Transpose macro

[grichey]

Hello,

I'm running this code and at the very end of running after succesfully moving a bunch of data correctly, I'm getting an error 1004 application error. Any idea what's causing this? I can just click ok and the data is how I want it but the error is annoying...

With ActiveSheet



.Rows(1).Insert

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

EndAt = LastRow

For i = LastRow To 1 Step -1



If .Cells(i, "A").Value = "" Then


'error @ next line
.Cells(i + 1, "A").Resize(EndAt - i).Copy

.Cells(i, "A").PasteSpecial Paste:=xlPasteValues, Transpose:=True

.Rows(i + 1).Resize(EndAt - i).Delete

EndAt = i - 1

End If

Next i

End With

[mdmackillop]

Hi Gavin,
You can't resize to 0.
This "works" but may not give the desired result.

Sub Test()
With ActiveSheet
.Rows(1).Insert
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
EndAt = LastRow
For i = LastRow To 1 Step -1
If .Cells(i, "A").Value = "" Then
'error @ next line
If (EndAt - i) <> 0 Then
.Cells(i + 1, "A").Resize(EndAt - i).Copy
.Cells(i, "A").PasteSpecial Paste:=xlPasteValues, Transpose:=True
.Rows(i + 1).Resize(EndAt - i).Delete
End If
EndAt = i - 1
End If
Next i
End With
End Sub

[grichey]

Thanks man.