Macro to delete 0

[Guest]

Is it possible to record something along the lines of

If Active.Cell = 0, then delete all 0s in column C - Y (for that same row only

The active cell would always be in column b

 

[Bob Phillips]

With Activecell
If .Value = 0 Then
Range("C" & .Row & ":Y" & .Row"").Replace What:="0", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
End WIth


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

When I entered this into the macro, from the word
Range ("C"... through.... MatchCase:=False
shows up in red and the "" after the second Row is highlighted.

 

[Bob Phillips]

Sorry, some extra ""

With ActiveCell
If .Value = 0 Then
Range("C" & .Row & ":Y" & .Row).Replace What:="0", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
End With


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

If I wanted to be able to select several cells, could ActiveCell be replaced with SelectedCells or something along the lines
----- Bob Phillips wrote: ----


With Activecel
If .Value = 0 The
Range("C" & .Row & ":Y" & .Row"").Replace What:="0",
Replacement:="",
LookAt:=xlWhole,
SearchOrder:=xlByRows,
MatchCase:=Fals
End I
End WIt


--

HT

Bob Phillip
... looking out across Poole Harbour to the Purbeck
(remove nothere from the email address if mailing direct

 

[Bob Phillips]

No, you would need to lop through them, but make sure only the cells in
column B are selected

For Each cell In Selection
If cell.Value = 0 Then
Range("C" & cell.Row & ":Y" & cell.Row"").Replace
What:="0", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
Next cell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

If I wanted to be able to select several cells, could ActiveCell be

replaced with SelectedCells or something along the lines?

 

[Frank Kabel]

Hi
try

dim rng as range
dim cell as range
set rng = selection
for each cell in rng
With cell
If .Value = 0 Then
Range("C" & .Row & ":Y" & .Row).Replace What:="0", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
End With
next

 

[Bob Phillips]

oops that mistake again

For Each cell In Selection
If cell.Value = 0 Then
Range("C" & cell.Row & ":Y" & cell.Row).Replace _
What:="0", _
Replacement:="", _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
MatchCase:=False
End If
Next cell


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)