Replace Function isn't working

I want to replace all blank cells in the group from J4 to the last row with a
-. Here is the macro that I wrote. However, when it runs, it replaces all
blank cells in the sheet, not just the ones that I want. Thank you for your
help.


With wks_E.Range("J4:J" & ExistingFinalRow)
Cells.Replace What:="", Replacement:="-", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

Remove the unqualifed "cells", so your replace command is anchored to your
range in column J

With wks_E.Range("J4:J" & ExistingFinalRow)
.Replace What:="", Replacement:="-", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End With

an alternative is to put a period in front of cells, but cells is not
necessary, so no use including it.

--
Regards,
Tom Ogilvy

"Adam" <Adam@discussions.microsoft.com> wrote in message
news:56CF6C09-6436-4E80-810E-FDF1793542CA@microsoft.com...
> I want to replace all blank cells in the group from J4 to the last row
with a
> -. Here is the macro that I wrote. However, when it runs, it replaces
all
> blank cells in the sheet, not just the ones that I want. Thank you for
your
> help.
>
>
> With wks_E.Range("J4:J" & ExistingFinalRow)
> Cells.Replace What:="", Replacement:="-", LookAt:=xlWhole, _
> SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
> ReplaceFormat:=False
> End With