-
Solved: Error 1004 in Transpose macro
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...
[VBA] 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[/VBA]
-
Hi Gavin,
You can't resize to 0.
This "works" but may not give the desired result.
[VBA]
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
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules