Fill/Copy Down Blank Cells Issue

I'm trying to fill down various columns based on the cell value above it, and have found various examples in how do so including the below macro.

But what I'm finding is that this does not always work. I have found that for cells that are formatted as Text, the macro returns the =R[-1]C instead the correct cell value.

Sub XFillInBlanks()
    Dim Rng1 As Range, Rng2 As Range
    Set Rng1 = Selection
    On Error Resume Next
    Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not Rng2 Is Nothing Then
        Rng2.FormulaR1C1 = "=R[-1]C"
        With Rng1
            .Value = .Value
        End With
    End If
End Sub

Is there a way to fill blank cells without having to first change the formatting back to General?

Thanks Leith,

I gave that code a try, however that seemed to copy the value in the first row down to the last in the selection overwriting any values in the middle. It should be copying down to the next non-blank cell, and then starting over with the new value it finds filling the blanks to the the next non blank.

I know these 2 samples I found partially work. but if the cell is formatted as text, it returns the "=R[-1]C" when it should be returning the cell value of that first non blank above it.

Both of these work if the cell if formatted as General. But if the formatting is Text it fails.

Sub XFillInBlanks()
    Dim Rng1 As Range, Rng2 As Range
    Set Rng1 = Selection
    On Error Resume Next
    Set Rng2 = Rng1.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If Not Rng2 Is Nothing Then
        Rng2.FormulaR1C1 = "=R[-1]C"
        With Rng1
            .Value = .Value
        End With
    End If
End Sub


Sub Fill_Blank_Cells()
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "=R[-1]C"
End Sub

Sorry to give this a bump, but i'm still struggling to solve this issue.

I'm trying to copy down to the next non-blank cell, and then continue on copying down, etc. And as mentioned above, these macro's work, but only if the cell is in a 'general' format. For those cells with text formatting, I'm getting a "=R[-1]C" value in the cell, incorrectly.

Does anyone know of a way to copy/fill down the blank cells when the cell is formatted as Text?

Sure. Here's an example of what I'm seeing and should help clarify things.

I think I just learned something too. It appears to be affected only if its the blank cells that are formatted at text.

So I think maybe just need to first format all blank cells back to general. Then you can copy the first non-blank cell down to the next filled one, etc.

Here's an option that works irregardless.

Sub ptmuldoon()
Dim rcell As Range
For Each rcell In Selection.SpecialCells(4)
    rcell.Value = rcell.Offset(-1).Value
Next rcell
End Sub