bobkap
Active Member
- Joined
- Nov 22, 2009
- Messages
- 323
- Office Version
- 365
- Platform
- Windows
- Mobile
- Web
In my 4th column - every time there is a blank cell I want to copy the data from the cell above the blank cell and paste it in what was the blank cell. I think I have that part figured out. I need help please on how to continue my loop from THAT cell forward. The closest I can get is for the loop to keep going back to the first row and run again and again. I have over 21K records so the macro either bombs out or runs forever. Is there some way I can get my loop to continue from the point right after it copied and pasted in that blank column?
I have this code so far followed by sample data:
Sub Paste_after_last_row()
'
' Paste_at first blank row
'
'Find the last used row in a sheet and copy and paste data below it.
finalrow = Cells(Rows.Count, 4).End(xlUp).Row
banana = 2
Dim ws As Worksheet
Set ws = ActiveSheet
For banana = 1 To finalrow
For Each cell In ws.Columns(4).Cells
If Len(cell) = 0 Then cell.Select: Exit For
Next cell
ActiveCell.Offset(-1, 0).Activate
ActiveCell.Copy
ActiveCell.Offset(1, 0).PasteSpecial
myrow = ActiveCell.Row
mycol = ActiveCell.Column
Next banana
'
End Sub
I have this code so far followed by sample data:
Sub Paste_after_last_row()
'
' Paste_at first blank row
'
'Find the last used row in a sheet and copy and paste data below it.
finalrow = Cells(Rows.Count, 4).End(xlUp).Row
banana = 2
Dim ws As Worksheet
Set ws = ActiveSheet
For banana = 1 To finalrow
For Each cell In ws.Columns(4).Cells
If Len(cell) = 0 Then cell.Select: Exit For
Next cell
ActiveCell.Offset(-1, 0).Activate
ActiveCell.Copy
ActiveCell.Offset(1, 0).PasteSpecial
myrow = ActiveCell.Row
mycol = ActiveCell.Column
Next banana
'
End Sub