Microsoft Excel - Can't select Blank Cells properly

So what I'm trying to do is fill in any blank cells with the cell value above them.  Here is my code so far.  It works sometimes but other times the last cell value doesn't fill down to the bottom of the range selected.  I went through the code step by step and I found out that it's failing at the Selection.SpecialCells(xlCellTypeBlanks).Select line.  For some reason it doesn't select the correct blank cells all of the time... Any ideas why?


Sub FillDownCells()
filldownresponse = MsgBox("This will paste values.  Are you sure you want to continue?", vbYesNo + vbQuestion)
    If filldownresponse = vbNo Then
    Exit Sub
    Else
    GoTo filldownresume
    End If
filldownresume:
With Selection
jasonrowtop = .Row
jasonfilldowncolumn = .Column
jasonrowbottom = .Row + .Rows.Count - 1
End With
On Error GoTo jasonhelp
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"
Range(Cells(jasonrowtop, jasonfilldowncolumn), Cells(jasonrowbottom, jasonfilldowncolumn)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Exit Sub
jasonhelp:
MsgBox "You didn't select a blank cell"
End Sub

It looks to me that if you don't have the right cells selected to start with, it might only find the bottom row in the selected column.  You might try using UsedRange:

With Selection
jasonrowtop = .Row
jasonfilldowncolumn = .Column
jasonrowbottom = .parent.usedrange.rows(.parent.usedrange.rows.count).row
End With

It gets a bit wordy, but essentially it returns the last row of the used range.  Taking this long way is a bit cleaner than just using the parent.usedrange.rows.count because there might be unused rows above the used range and they wouldn't be included in the rows.count property.

Hi Jason,

I haven't seen your spreadsheet, but from the code attached, I think that the selected range is not cover all your data. 


    With Selection
    jasonrowtop = .Row
    jasonfilldowncolumn = .Column
    jasonrowbottom = .Row + .Rows.Count - 1 
'The line above should be wrong both column reference and row reference.

I want to suggest you to attach your file here so that I have more clear of your work...

pichart Y.

Fill Blanks Via a Macro

To use this, go to Tools>Macro>Visual Basic Editor (Alt+F11) then to Insert>Module and then paste in the code below.

Sub FillBlanks()
Dim rRange1 As Range, rRange2 As Range
Dim iReply As Integer
    If Selection.Cells.Count = 1 Then
      MsgBox "You must select your list and include the blank cells", _
        vbInformation, "OzGrid.com"
        Exit Sub

    ElseIf Selection.Columns.Count > 1 Then
      MsgBox "You must select only one column", _
        vbInformation, "OzGrid.com"
        Exit Sub
    End If

    Set rRange1 = Range(Selection.Cells(1, 1), _
      Cells(65536, Selection.Column).End(xlUp))

    On Error Resume Next
    Set rRange2 = rRange1.SpecialCells(xlCellTypeBlanks)
    On Error GoTo 0
    If rRange2 Is Nothing Then
      MsgBox "No blank cells Found", _
        vbInformation, "OzGrid.com"
      Exit Sub
    End If
    rRange2.FormulaR1C1 = "=R[-1]C"
    iReply = MsgBox("Convert to Values", vbYesNo + vbQuestion, "OzGrid.com")
    If iReply = vbYes Then rRange1 = rRange1.Value
End Sub

After pasting in the above code, click the top right X to get back to Excel and Save. Now go to Tools>Macro>Macros (Alt+F8) select FillBlanks and click Run, or Options to assign a shortcut key.

I tried this method but I still have a problem that the last value in the column doesn't fill down to the last cell in the range that I've selected. 

I also tried your method Anoop and I have the same problem that the last value in the column doesn't fill down to the last cell of the range that I've selected.  For example lets say I have a  1 in cell b2 and a 2 in cell b4 for simplicity.  Then I select the range b2:b8.  I would want a 1 to be filled into cells b2 and b3.  I would also want the value 2 to be filled in cells b4 to b8.  Obviously I'm normally working with a greater number of values but thats the concept.  Thank you for your help so far though, it is much appreciated.

Alrighty then, this one works:


It reads the entire selection into an array, scrolls through the array finding the blanks and replacing them with your formula.  Then it posts the array back to the spreadsheet, and copies/pastes special the values.  The problem with the original procedure was when it selected the blank cells, it didn't recognize cells past the UsedRange.  This version doesn't care what the UsedRange is...

You'll see this faster if I reply to you rather than myself...

Haha thanks for noticing that.  Code worked perfectly.  Thanks for all your help.

Jason