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
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" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>.
>
A non-macro option (this will only make the cells blank...it won't actually delete them):
Use an IF statement along with your current function...for instance if C5 gets the sum of C1-C4 you could type:
=IF(SUM(C1:C4)=0," ",SUM(C1:C4)) - this will put a space in the cell instead of a zero
OR...if the zeros aren't the result of a function and you want to get rid of them (once again this won't delete the cells)...you could hit CTRL+F for the find box...type in 0, click on 'Replace', type in a space and hit 'Replace All'
Thanks guys....both methods works!!!!!!
<[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>>.
>>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks