Sub to clear cells with zeros or null strings within a selected ra

[Max]

Hi guys,

I'm looking for a sub to clear cells with zeros or null strings within a
selected range. I'd select the range, then run the sub to clear all such
cells within the range. Thanks for insights.

 

[Jay]

Hi Max -

Although VBA variables can evaluate to "Null", worksheet cells cannot. A
cell is considered empty if it evaluates to a zero-length string (zls),
designated as "". So the procedure below sets cells in the selection to ""
if they containing a zero or spaces only.

Sub MaxSingapore()
Set rng = Selection
For Each itm In rng
If Trim(itm.Value) = "" Or itm.Value = 0 Then itm.Value = ""
Next 'itm
End Sub

 

[Max]

Thanks, Jay. But I want the sub to clear such cells (ie like CTRL-selecting
these cells at one go, then pressing the Delete key), not set these cells to
"". How would your sub then look like ? The selected range may contain cells
with "" and cells with zeros.

 

[Jay]

Sub MaxSingapore()
Set rng = Selection
For Each ar In Selection.Areas
For Each itm In ar
If Trim(itm.Value) = "" Or itm.Value = 0 Then itm.Clear
Next 'itm
Next 'ar
End Sub

 

[Max]

Runs great, Jay. Many thanks.

I changed: itm.Clear
to itm.ClearContents
to suit my purpose

 

[Dave Peterson]

You want to clean up the stuff left over from changing ="" to values (as well as
0's)?

Record a macro when you do 3 edit|Replaces
select your range
first one:
edit|Replace
what: 0
with: (leave blank)
(remember to match entire cell!)

Second:
edit replace
what: (leave blank)
with: $$$$$
replace all

Last one
edit|replace
what: $$$$$
with: (leave blank)

Use a nice unique string if $$$$$ is used somewhere in that range (don't use
apostrophes!).

 

[Max]

Thanks for the response, Dave. Yes, that does it too, but I was also looking
for a generic way which would apply to any selection(s) irrespective. The
macro recorder doesn't give that.

 

[Dave Peterson]

You can tweak your recorded macro to use the Selection instead of any specific
range:

Option Explicit
Sub testme()
Dim myRng As Range
Set myRng = Selection
With myRng
.Replace What:="0", Replacement:="", LookAt:=xlWhole, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="", Replacement:="$$$$$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
.Replace What:="$$$$$", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub

 

[Max]

Thanks for the tweak, Dave. Runs good!