Filling any empty cells with the value above

Question

I want to fill in all empty cells using values of above cells

state  name
IL     Mike 
       Sam
CA     Kate
       Bill
       Leah

Should be as follows

   state  name
    IL     Mike 
    IL     Sam
    CA     Kate
    CA     Bill
    CA     Leah

I tried the following

Sub split()
Dim columnValues  As Range, i As Long 

Set columnValues = Selection.Area

Set i = 1
For i = 1 To columnValues.Rows.Count   
    If (columnValues(i) = "") Then
    columnValues(i) = columnValues(i - 1)
    End If
Next

End Sub

I get an error when I set i. How can I modify my code

Answer 1

For those not requiring VBA for this, select ColumnA, Go To Special..., Blanks and:

Equals (=), Up (▲), Ctrl+Enter

should give the same result.

Answer 2

Given you asked for VBA, there is a quicker way than looping (the VBA equivalent of what pnuts posed above, with the additional step of removing the formula at the end):

On Error Resume Next
With Selection.SpecialCells(xlCellTypeBlanks)
.FormulaR1C1 = "=R[-1]C"
.Value = .Value
End With