Recently, I needed to write a macro to replace all NULL values in a spreadsheet with a “0.00”. This seems easy, but if you write a default search and replace in your macro, Excel will dutifully replace *every* *single* NULL in your columns, even where there is no adjacent data. This means that your spreadsheet will grow to the maximum number of rows, because you added zeros to ALL NULL cells in the column. To only replace NULL values where there is adjacent data, you need to find the “LastRow” in your spreadsheet.

If you have to replace values in more than one column, you’ll need to rename the variable declarations as below in this example: (Note the Area, Area1, Area2, LastRow, LastRow1, LastRow2, etc. below)

## MACRO SNIP ##

Columns("D:D").Select
  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 = "0.00"
  Next

Columns("E:E").Select
  Dim Area1 As Range, LastRow1 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 = "0.00"
  Next

Columns("F:F").Select
  Dim Area2 As Range, LastRow2 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 = "0.00"
  Next

This way, you get zeros just until the last row, and no more! Replace the zeros in the value of Area.Value = “0.00” with whatever you want put into the NULL cells!