Sub FillColBlanks_Offset() 'by Rick Rothstein 2009-10-24 'fill blank cells in column with value above 'http://www.contextures.com/xlDataEntry02.html Dim Area As Range, LastRow As Long On Error Resume Next LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, _ SearchDirection:=xlPrevious, _ LookIn:=xlFormulas).Row For Each Area In ActiveCell.EntireColumn(1).Resize(LastRow). _ SpecialCells(xlCellTypeBlanks).Areas Area.Value = Area(1).Offset(-1).Value Next End Sub