VBA: Search for empty cells in column and replace fill will the previous cell in same column

[MarcWiseman]

Hey all,

This seems like it should be ridiculously easy. But how would you write a macro that searches for empty cells in a column, in my case column B, and pastes the previous cell in the column in there.

For example, in my column A row 1 has 103.1, followed by 4 blank cells. Row 6 has 117.5 followed by 10 blank cells, etc. I want the blank cells in row 2-5 to have 103.1 and rows 7-16 to read 117.5.

I hope this is clear.

thanks in advance.

Marc

 

[Tuelor]

I would do the following:

• 
  Start recording a macro
  Create an if statement in column B. If column A has a value then show it, else show cell before.
  Copy formula down, and paste column values in to column A
  Clear column B
  Stop recording

D

 

[MarcWiseman]

sorry I had a typo in my question...Where I say column A I meant column B

 

[tinyskills13]

You can use a for loop for each cell in the column, and it might help to use relative references. This will give the active cell the value of the one above it:
ActiveCell.FormulaR1C1 = "=R[-1]C"

 

[Rick Rothstein]

This macro should do what you want...

Sub FillInTheBlanks()
  Dim LastRow As Long
  Const DataStartRow As Long = 2
  Const ColumnToFill As String = "A"
  LastRow = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
  Cells(DataStartRow, ColumnToFill).Resize(LastRow - DataStartRow + 1).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
  Columns(ColumnToFill).Value = Columns(ColumnToFill).Value
End Sub

Note: There are two constants (the Const statements) that you need to make sure are set for your own setup. I guessed the data starts on Row 2 (Row 1 being a header) and the blanks to be filled in are in Column A.

 

[MarcWiseman]

worked great thanks Rick!