Amend code to run faster

I have the following code kindly supplied to me by someone else and I'd like to know if anyone can suggest any ways to speed up the running of it. It currently takes about 4 minutes to run (there are about 5000 cells to loop through)

Basically it looks for a value in Col C - works out the number of rows to fill up by searching a range in Col D - then copies value in Col C up by that number of rows.

Sub Fill_In_Reason()
Dim lastRow As Long
Dim myRow As Long
Dim myCel As String
Dim myRng As String
lastRow = Range("C65530").End(xlUp).Row
    For Each cell In Range("C2:C" & lastRow).SpecialCells(xlCellTypeConstants, 23)
        myCel = cell.Address
        Debug.Print cell.Address
        myRow = Range(myCel).Row
With Range("D2:D" & myRow).SpecialCells(xlCellTypeConstants)
    myRng = Range("D2:D" & lastRow).SpecialCells(xlCellTypeConstants).Areas(.Areas.Count).Offset(0, -1).Address
    Range(myCel).Copy Range(myRng)
End With
    Next cell
End Sub

Any help would be appreciated

Regards

Seamus

Sure can, roy. Sample book attached.

Column C contains reason for activity (sickness, maternity leave etc). Column D contains the staff who worked those shifts.

Currently the reason for activity is only in the subtotal cell of col C and I need to "fill up" those reasons by the number of cells in Col D.

I'd be grateful if you's take a look at the book and advise me on the best way forward.

Regards

Seamus

Sorry roy. But that appears to change the values in Col D. Will keep looking at it though and try to figure out something.

Thanks

Seamus

Many many thanks roy,

With just a slight adjustment in the code it now woeks like a charm

Sub Fill_In_Reason()
Dim lastRow As Long
Dim rng   As Range
Dim cl     As Range
lastRow = Range("F65530").End(xlUp).Row
    Set rng = Range("D2:D" & lastRow)
    For Each cl In rng
    If Not IsEmpty(cl) Then _
        cl.Offset(0, -1).Value = cl.Offset(0, -1).End(xlDown).Value
    Next cl
End Sub

Regards

Seamus

roy,

I Debug.Printed using your original code and it moved as follows, D2, E2, F2, D3, E3, F3 etc - I realise now that was because of the typo 6 instead of 4 - but I am now using your amended code.

Thanks

Seamus

Absolutely - this code just flies through in a matter of seconds while the old code looping through took between 4 and 5 minutes.

Good work

Seamus