I have this code:

Sub CopyOverCompleted()
Dim a As Long
Dim i As Long
With Sheets("Status")
i = .Cells(Rows.Count, "B").End(xlUp).Row
For a = 4 To i
If .Cells(a, "L").Value < "" Then .Cells(a,
"L").EntireRow.Cut _
Sheets("Completed").Cells(Rows.Count,
1).End(xlUp).Offset(1, 0)
Next a
End With

LastRow = Sheets("Status").UsedRange.Row - 1 + _
Sheets("Status").UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 4 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
Application.ScreenUpdating = True

End Sub

which cuts and deletes completed projects (rows) to the completed
sheet. Is there a way to shorten and improve the code. TIA

Greg

It looks okay. Is there a problem with it?

--
HTH

Bob Phillips

"GregR" wrote in message
oups.com...
I have this code:

Sub CopyOverCompleted()
Dim a As Long
Dim i As Long
With Sheets("Status")
i = .Cells(Rows.Count, "B").End(xlUp).Row
For a = 4 To i
If .Cells(a, "L").Value < "" Then .Cells(a,
"L").EntireRow.Cut _
Sheets("Completed").Cells(Rows.Count,
1).End(xlUp).Offset(1, 0)
Next a
End With

LastRow = Sheets("Status").UsedRange.Row - 1 + _
Sheets("Status").UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 4 Step -1
If Application.CountA(Rows(r)) = 0 Then Rows(r).Delete
Next r
Application.ScreenUpdating = True

End Sub

which cuts and deletes completed projects (rows) to the completed
sheet. Is there a way to shorten and improve the code. TIA

Greg

Bob, no, everything works. I just thought there might be a better way
to cut and delete more efficiently. TIA

Greg

You could use AUtofilter which would be quicker on a large dataset, but as
much (or more) code.

--
HTH

Bob Phillips

"GregR" wrote in message
oups.com...
Bob, no, everything works. I just thought there might be a better way
to cut and delete more efficiently. TIA

Greg

or sort on col "L"
then size the range and cut/copy in 1 block


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Bob Phillips wrote :

You could use AUtofilter which would be quicker on a large dataset,
but as much (or more) code.

Bob, KeepITcool, thanks

Greg

Hi. I'm not sure how your sheet is set up, but would there be any ideas
here that may help? I assume you have constants in column L, but you can
change that. One option would be to take the union of both Constants and
Formulas in Column L if you needed to. The idea here is that you can copy
multiple areas at one time vs Cut. Here, I did not look at Column B.


Sub Demo()
Dim rFRom As Range
Dim rTo As Range
Dim C As Long 'Column #

Set rTo = Sheets("Completed").Cells(Rows.Count, 1).End(xlUp)(2, 1)

On Error Resume Next
C = [L1].Column
Set rFRom = Range(Cells(4, C), Cells(Rows.Count,
C)).SpecialCells(xlCellTypeConstants) ' or xlCellTypeFormulas
If Err.Number 0 Then Exit Sub

rFRom.EntireRow.Copy rTo
rFRom.EntireRow.Delete
End Sub

--
Dana DeLouis
Win XP & Office 2003


"GregR" wrote in message
ps.com...
Bob, KeepITcool, thanks

Greg

Dana, I love the ideas that you pros share so unselfishly. Works
perfectly. Thanks

Greg