deleting cell with 0
 

How do I set a macro to delete all 0's in a selected range or is there a
function to do this?

Thanks in advance for your replies.

Aloysicus

Assuming the range is column A, this will do it

Dim cLastRow As Long
Dim i As Long

cLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = cLastRow To 1 Step -1
If Cells(i, "A").Value = 0 Then
Cells(i, "A").EntireRow.Delete
End If
Next i


--

HTH

RP
(remove nothere from the email address if mailing direct)


"Aloysicus" wrote in message
...
How do I set a macro to delete all 0's in a selected range or is there a
function to do this?

Thanks in advance for your replies.

Aloysicus

Select your range and run this macro:

Sub DeleteZero()
Dim NumRng As Range
Dim cell As Range
On Error GoTo ErrorMsg
Set NumRng = ActiveSheet.Cells _
.SpecialCells(xlCellTypeConstants, 1)
For Each cell In Intersect(Selection, NumRng)
If cell.Value = 0 Then cell.ClearContents
Next
Exit Sub
ErrorMsg:
MsgBox "No cells found!"
End Sub

---
To run, press ALT+F11, go to Insert Module, and paste
in the code above. Press ALT+Q to close. Go to Tools
Macro Macros.

HTH
Jason
Atlanta, GA

-----Original Message-----
How do I set a macro to delete all 0's in a selected
range or is there a
function to do this?

Thanks in advance for your replies.

Aloysicus


.

Thanks guys....both methods works!!!!!!
wrote in message
...
Select your range and run this macro:

Sub DeleteZero()
Dim NumRng As Range
Dim cell As Range
On Error GoTo ErrorMsg
Set NumRng = ActiveSheet.Cells _
.SpecialCells(xlCellTypeConstants, 1)
For Each cell In Intersect(Selection, NumRng)
If cell.Value = 0 Then cell.ClearContents
Next
Exit Sub
ErrorMsg:
MsgBox "No cells found!"
End Sub

---
To run, press ALT+F11, go to Insert Module, and paste
in the code above. Press ALT+Q to close. Go to Tools
Macro Macros.

HTH
Jason
Atlanta, GA

-----Original Message-----
How do I set a macro to delete all 0's in a selected
range or is there a
function to do this?

Thanks in advance for your replies.

Aloysicus


.