How to Fill the data
 

I have some historical data with some missing values for a certain
dates, such as holiday.

I need to replace all the missing value with previous date's data so
that I can do some analysis on it.

Here is the program. It works fine but it is extremely slow. it even
take more than 2 hours to run just 8000 cells!!!

Why is it so slow? Anyway to make it fast?

Thanks a lot,

ron

Private Sub filldata()

Sheets("Data").Select
NumofCell = Range("E9", Range("E9").End(xlDown)).Count

For i = 1 To NumofCell

If Not IsNumeric(Cells(i + 8, 5)) Then
Cells(i + 8, 5).value = Cells(i + 7, 5).value
End If
Next i

End Sub

How to Fill the data
 

First, try using this to get the last cell:
NumofCell = Cells(Rows.Count, "E").End(xlUp).Row

when I tried yours I got all 60,000+ of em.

and turn off screen updating

Application.ScreenUpdating = False

don't forget to turn it back on, 8 hours is rediculous at any rate.

-John

"ronwang1001" wrote:

I have some historical data with some missing values for a certain
dates, such as holiday.

I need to replace all the missing value with previous date's data so
that I can do some analysis on it.

Here is the program. It works fine but it is extremely slow. it even
take more than 2 hours to run just 8000 cells!!!

Why is it so slow? Anyway to make it fast?

Thanks a lot,

ron

Private Sub filldata()

Sheets("Data").Select
NumofCell = Range("E9", Range("E9").End(xlDown)).Count

For i = 1 To NumofCell

If Not IsNumeric(Cells(i + 8, 5)) Then
Cells(i + 8, 5).value = Cells(i + 7, 5).value
End If
Next i

End Sub