Efficient Code

[GregR]

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 Phillips]

It looks okay. Is there a problem with it?

 

[GregR]

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

Greg

 

[Bob Phillips]

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

 

[keepITcool]

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 :

 

[GregR]

Bob, KeepITcool, thanks

Greg

 

[Dana DeLouis]

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

 

[GregR]

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

Greg