PDA

View Full Version : Whats the best way to delete the lines i just copied and pasted over (code inside)



markpem
06-03-2015, 06:34 AM
Hello

I have some working code (below) which filters column O in worksheet "Main Sheet" for the word "COMPLETED" and copies it, then jumps to the "Completed" Sheet and finds the last unused row and pastes it there.

What is the best (and cleanest) way to delete all the items i just copied over back on the main sheet?

Thanks for all your time and trouble



Sheets("Main Sheet").Activate
lastRow = Range("A" & Rows.Count).End(xlUp).Row


ActiveSheet.Range("$A$1:$AC$" & lastRow).AutoFilter Field:=15, Criteria1:= _
"COMPLETED"
Range("A2:AC" & lastRow).Select
Selection.Copy

Sheets("Completed).Select
lastrow2 = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lastrow2+1).Select

ActiveSheet.Paste

SamT
06-03-2015, 07:17 AM
Try this and see

Sub SamT()
Dim PasteTo As Range
Dim LastRow As Long

'Set up Copy 'Destination'
Set PasteTo = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)

With Sheets("Main Sheet")
.Range("A:AC").AutoFilter Field:=15, Criteria1:="COMPLETED"
LastRow = Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:AC" & LastRow).Copy PasteTo
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
End Sub

markpem
06-03-2015, 07:34 AM
Hello SamT

Thanks for your reply. Your code certainly does look cleaner and more to the point, however it's falling over at this bit:-




.SpecialCells(xlCellTypeVisible).EntireRow.Delete


with the error code

Run-time error '438':
Object doesn't support this property or method

Thanks

P.S. It actually copies over all the data to the correct sheet, just not delete the data from the first sheet


EDIT: I *think* I have got it working, but could you let me know if this is a safe method?




Dim PasteTo As Range
Dim LastRow As Long

'Set up Copy 'Destination'
Set PasteTo = Sheets("Completed").Range("A" & Rows.Count).End(xlUp).Offset(1)

With Sheets("Main Sheet")
.Range("A:AC").AutoFilter Field:=15, Criteria1:="Completed"
LastRow = Range("A" & Rows.Count).End(xlUp).Row
.Range("A2:AC" & LastRow).Copy PasteTo

ActiveSheet.Range("$A$1:$AC" & LastRow).Offset(1, 0).SpecialCells _
(xlCellTypeVisible).EntireRow.Delete

End With

SamT
06-03-2015, 08:41 AM
That is basically the same as I had. If your table is also Sorted so that all the "COMPLETED" rows are in a block when not Filtered, then

ActiveSheet.Range("$A$1:$AC" & LastRow).Delete Shift:=xlUp