Fastest way to replace blank cells?

[JP Romano]

Hello - have gotten some great advice from your site, so I figured I'd join and see how much more I can learn. Not a programmer, but am working on a project which requires me to do some VBA programming (via Excel 07).

I'm currently trying to find a fast way to replace every blank (empty) cell in a range with a text string "No Data Available"

My spreadsheet is over 28,000 rows, and the cells which need replacing are in columns D, E, F, and G. It's working now, but I need to try to shave off some time from the execution of the macros... any advice is greatly appreciated!

Sub replaceblanks()
'
'Macro will replace blanks with "No Data Available"

application.calculation = xlCalculationManual
application.screenupdating = false
application.displayalerts = false

Range("D2:G28230").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
     False, SearchFormat:=False).Activate
     Selection.replace What:="", Replacement:="No Data Available", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Calculate

application.calculation = xlCalculationAutomatic
application.screenupdating = true
application.displayalerts = true


End Sub

[ubentook]

You don't need to do the find. Just use replace.
Also, selecting is not necessary...
'--
Sub replaceblanks_R 1()
'Macro will replace blanks with "No Data Available"
Application.Cal culation = xlCalculationMa nual
Application.Scr eenUpdating = False
Application.Dis playAlerts = False

Range("D2:G2800 0").Replace What:="", _
Replacement:="N o Data Available", _
LookAt:=xlPart, SearchOrder:=xl ByRows, _
MatchCase:=Fals e, SearchFormat:=F alse, _
ReplaceFormat:= False

Application.Cal culation = xlCalculationAu tomatic
Application.Scr eenUpdating = True
Application.Dis playAlerts = True
End Sub
'--
As for myself, I would probably use...
Set rng = Range("D2:G2800 0").SpecialCell s(xlCellTypeBla nks)
rng.Value = "No Data Available"
'--
Note: xl2007 can be much slower than earlier versions.

[JP Romano]

Wow...that certainly helped - I really appreciate the info. I've used the same syntax elsewhere in my workbook and I'm sure it'll speed things up!