Microsoft Excel - Can't select Blank Cells properly

Asked By Jason on 10-Nov-11 12:42 PM
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
    GoTo filldownresume
    End If
With Selection
jasonrowtop = .Row
jasonfilldowncolumn = .Column
jasonrowbottom = .Row + .Rows.Count - 1
End With
On Error GoTo jasonhelp
Selection.FormulaR1C1 = "=R[-1]C"
Range(Cells(jasonrowtop, jasonfilldowncolumn), Cells(jasonrowbottom, jasonfilldowncolumn)).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Exit Sub
MsgBox "You didn't select a blank cell"
End Sub
wally eye replied to Jason on 10-Nov-11 04:49 PM
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.
Pichart Y. replied to Jason on 10-Nov-11 11:16 PM
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.

Anoop S replied to Jason on 11-Nov-11 12:58 AM
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, ""
        Exit Sub

    ElseIf Selection.Columns.Count > 1 Then
      MsgBox "You must select only one column", _
        vbInformation, ""
        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, ""
      Exit Sub
    End If
    rRange2.FormulaR1C1 = "=R[-1]C"
    iReply = MsgBox("Convert to Values", vbYesNo + vbQuestion, "")
    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.
Jason replied to wally eye on 11-Nov-11 04:24 PM
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. 
Jason replied to Anoop S on 11-Nov-11 04:49 PM
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.
wally eye replied to wally eye on 12-Nov-11 05:22 PM
Alrighty then, this one works:

Public Sub FillDownCells()
  Dim arrFormulas     As Variant
  Dim intReply        As Integer
  Dim lngCurrRow      As Long
  intReply = MsgBox("This will paste values.  Are you sure you want to continue?", _
    vbYesNo + vbQuestion)
  If intReply = vbYes Then
    arrFormulas = Selection.FormulaR1C1
    For lngCurrRow = LBound(arrFormulas) To UBound(arrFormulas)
      If arrFormulas(lngCurrRow, 1) = "" Then
        arrFormulas(lngCurrRow, 1) = "=R[-1]C"
      End If
    Next lngCurrRow
    Selection = arrFormulas
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
      SkipBlanks:=False, Transpose:=False
  End If
End Sub

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...
wally eye replied to Jason on 12-Nov-11 05:51 PM
You'll see this faster if I reply to you rather than myself...
Jason replied to wally eye on 17-Nov-11 12:18 PM
Haha thanks for noticing that.  Code worked perfectly.  Thanks for all your help.