find range and fill down

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

range starts at c25.
need to find last cell used in column c.
highlight/select all cells in between. = range
fill down any empty spaces with value from cell above (within range)

(list/data will be incomplete. need to fill in empty spaces only
within used range down, but not to extend past last used cell).

thanx
 
This macro should execute quicker...

Sub FillInTheBlanks()
Dim Area As Range, LastRow As Long
On Error Resume Next
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
For Each Area In Range("C25").Resize(LastRow). _
SpecialCells(xlCellTypeBlanks).Areas
Area.Value = Area(1).Offset(-1).Value
Next
End Sub
 
Another one.

Sub fillinblank()
Dim startrow As Long, i As Long
Dim Lastcell As Range, Blankrng As Range

startrow = 25
Set Lastcell = Cells(Rows.Count, "C").End(xlUp)
Set Blankrng = Range(Cells(startrow, "C"), Lastcell) _
.SpecialCells(xlCellTypeBlanks)

For i = 1 To Blankrng.Areas.Count
Blankrng.Areas(i) = Blankrng.Areas(i).Resize(1, 1).Offset(-1).Value
Next

End Sub

Keiji
 
For the most part, my macro does exactly what your macro does; however, as
written, it copies down to the end of the UsedRange for the worksheet. The
following minor modification makes my macro duplicate the results of your
macro...

Sub FillInTheBlanks()
Dim Area As Range, LastRow As Long
On Error Resume Next
LastRow = Cells(Rows.Count, "C").End(xlUp).Row
For Each Area In Intersect(Rows("25:" & LastRow), Range("C25"). _
Resize(LastRow).SpecialCells(xlCellTypeBlanks)).Areas
Area.Value = Area(1).Offset(-1).Value
Next
End Sub

Because it is handling empty Areas at a time, this should still be faster
than iterating each individual cell (unless there is no groupings of empty
cells, that is, unless each empty cell stands alone).
 
Back
Top