I am looking to copy paste and delete a entire row based on if Column A has"xyz" ie:If A1="xyz" then copy paste(a1:g1) into next blank row and delete row 1:1.
Asked
Active
Viewed 775 times
-3
-
1. Do you want to paste in same sheet or other sheet? 2. What do you mean by Next Balnk row, Is it after the last row of sheet or in between blank rows in sheet? – Paresh J Oct 06 '14 at 06:55
-
On same sheet, last row of the sheet – Parthasarathy R Oct 06 '14 at 07:05
-
See if [THIS](http://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s) helps – Siddharth Rout Oct 06 '14 at 07:36
-
Is it only the one row (i.e. row 1) or do you want to loop through a range? – Mark Fitzgerald Oct 06 '14 at 09:52
-
Its a loop through a range – Parthasarathy R Oct 06 '14 at 12:52
1 Answers
1
A custom sort that places xyz after all else might be the better solution. If you need to copy/paste/delete then remember that it is always best to start at the bottom and work up in your For ... Next loop when deleting rows.
Dim r As Long, lr As Long
With ActiveSheet
lr = .Cells(Rows.count, 1).End(xlUp).Row
For r = lr To 1 Step -1
If LCase(.Cells(r, 1)) = "abc" Then
.Cells(r, 1).Resize(1, .Cells(r, Columns.count).End(xlToLeft).Column).Copy _
Destination:=.Cells(Rows.count, 1).End(xlUp).Offset(1, 0)
.Rows(r).Delete
End If
Next r
End With
-
1@pnuts - That *abc* Easter egg has traditionally been a tough nut to crack. :) – Nov 27 '14 at 19:16