Fill in Blank Cells

[maliti2008]

I have a column of Names that that has some empty fields. I need to complete that data, by filling the blank cells with the last non-balnk cell prior to that cell.

For example, Cell b5 has a name in it and the 5 cells below it are empty. i want to fill those empty cells by the value of cell b5 and start that process over and over

the blank cells dont have a set number in between.

Thoughts?

 

[MARK858]

If the cells are truly blank then try either

Sub FillCell1()
    Dim fCell As Range
    On Error Resume Next

    For Each fCell In Range("B5:B" & Range("B" & Rows.Count).End(xlUp).Row). _
        SpecialCells(xlCellTypeBlanks).Areas
        fCell.Value = fCell(1).Offset(-1).Value
    Next

    On Error GoTo 0
End Sub

or

Sub FillCell2()
    On Error Resume Next

    With Range("B5:B" & Range("B" & Rows.Count).End(xlUp).Row)
        .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
        .Value = .Value
    End With

    On Error GoTo 0
End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub FillNames()
   With Range("B1", Range("A" & Rows.Count).End(xlUp).Offset(, 1))
      .SpecialCells(xlBlanks).FormulaR1C1 = "=r[-1]c"
      .Value = .Value
   End With
End Sub

This will fill the blanks in column B to the last used cell in column A

 

[Rick Rothstein]

Assuming Column B contains your names, give this macro a try...

[table="width: 500"]
[tr]
	[td]Sub FillBlanks()
  Dim LastRow As Long
  LastRow = Cells.Find("*", , xlValues, , xlRows, xlPrevious, , , False).Row
  With Range("B1:B" & LastRow)
    .SpecialCells(xlBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
  End With
End Sub[/td]
[/tr]
[/table]

EDIT NOTE: This solution is similar to the one Fluff posted except that his code requires you to specify a column containing the last most data entry (he assumed Column A) so that he can determine the what the last row of data is whereas my code figures out the last row of data on its own.

 

[NdNoviceHlp]

This seems to do it. Dave

Sub test()
Dim Lastrow As Integer, Cnt As Integer, Temp
Temp = "No Value"
With ActiveSheet
Lastrow = .Cells(Rows.Count, "B").End(xlUp).Row
For Cnt = 1 To Lastrow
If .Cells(Cnt, "B") <> vbNullString Then
Temp = .Cells(Cnt, "B")
Else
.Cells(Cnt, "B") = Temp
End If
Next Cnt
End With
End Sub

 

[mole999]

something like

Sub fixer()
Dim n
    Dim LR As Long
    LR = Worksheets("test").Cells(Worksheets("test").Rows.Count, "B").End(xlUp).Row + 1
    For n = 1 To LR
    If Range("B" & n) = "" Then Range("B" & n) = Range("B" & n - 1)
    Next n
    
End Sub

 

[MARK858]

EDIT NOTE: This solution is similar to the one Fluff posted except that his code requires you to specify a column containing the last most data entry (he assumed Column A) so that he can determine the what the last row of data is whereas my code figures out the last row of data on its own.

All the codes (including mine) require the OP to state how the finish point should be defined whether it be the last used cell in columns A, B, some other column, All columns or some other definition which they haven't stated as yet (and actually the start point also needs clarifying as well).

 

[maliti2008]

Wow! where have you guys been all my life!! this is amazing! thank you!!!!!!!!!!!!!!!

 

[sandy666]

or

1. Select the range that contains blank cells you need to fill.
2. Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option
3. Click OK, and all of the blank cells have been selected. Then input the formula “=A2 ” into active cell A3 without changing the selection. This cell reference can be changed as you need.
4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells

 

[MARK858]

Then input the formula “=A2 ”
[/LIST][/COLOR][/URL]

Better to press the "=" button followed by pressing the "up" arrow... more generic

Btw, if not using the "up" arrow then the formula should have been changed to reference a cell in column B as that is the column the OP is dealing with