Autofill 1 column with changing data and changing range

[cdclayton]

Excel 2003
I have one column of data which has about 500 values within 4,000
cells. So I have a value in A1 (e.g. "red") and then blanks until A6
(e.g. "blue23") when it changes values and then blanks until A23 (e.g.
"15abc"). What I would like to do is to take the value in A1 ("red")
and copy it down and fill all the blanks and then take the new value
found in A6 ("blue23") and copy that down until it gets to A23, etc.
There is no pattern to the blanks spots or the values. Sometimes I
have 3 blanks between values and other times 23 blanks. In the column
to the right of this data I have values in all cells so I know that I
can doubleclick the value in A1 and get the autofill to work for me.
However, since the data is so much, is there a way to do this through
VBA otherwise I will be clicking for a while.

Thanks,

Charles D Clayton Jr

 

[dmoney]

Sub fill()
'
Range("a1").Select
Do Until ActiveCell.offset(0,1).Value = ""
If ActiveCell.Value <> "" Then
Dim a As String
a = ActiveCell.Address
ActiveCell.Offset(1, 0).Activate
Count = 1
End If
Do Until ActiveCell.Value <> ""
If ActiveCell.Value = "" Then
ActiveCell.Offset(1, 0).Activate
Count = Count + 1
End If
Loop
Dim b As String
ActiveCell.Offset(-1, 0).Activate
b = ActiveCell.Address
Range(a & ":" & b).FillDown
ActiveCell.Offset(1, 0).Activate
Loop
'
End Sub