Deleting zero's within a range?

[Big H]

Hi there,

I have a range of data showing positive numbers, negative numbers and zeros.
The range can extend down to row 10000 or more. Is it possible to delete all
zero values within the range say B10:AA10000 and just leave the positive and
negative values.

thanks Harry

 

[Joergen Bondesen]

Hi Harry

Try this, please

Option Explicit

Sub DeleteZero()

Dim rrange As Range
Dim cell As Range

Set rrange = ActiveSheet.Range("B10:AA10000")

For Each cell In rrange
If Not cell.HasFormula Then
If Trim(cell.Value) = 0 Then
cell.Value = vbNullString
End If
End If
Next cell
End Sub

 

[JE McGimpsey]

One way:

Public Sub ClearZeros()
Dim rCheck As Range
Dim rCell As Range
On Error Resume Next
Set rCheck = Range("B10:AA10000").SpecialCells _
(xlCellTypeConstants, xlNumbers)
On Error GoTo 0
If Not rCheck Is Nothing Then
For Each rCell In rCheck
With rCell
If .Value = 0 Then .ClearContents
End With
Next rCell
End If
End Sub