[SOLVED] Filling any empty cells with the value above

Filling any empty cells with the value above

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

Solution

It is because i should be defined as i=1. There are although a few other problems with the code. I would change it to something like this:

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

    Set columnValues = Selection

    For i = 1 To columnValues.Rows.Count
        If columnValues.Cells(i, 1).Value = "" Then
            columnValues.Cells(i, 1).Value = columnValues.Cells(i - 1, 1).Value
        End If
    Next
End Sub