CODEWITHSUNDEEP
excelvba
user2573125
user2573125

Reputation: 11

Removing Zeros in excel using macros

I got some code from another post that helped me format code that enables me to remove zeros in a given column, its content and comments. However the code is also deleting the first cell (which is not a 0 and does not contain a zero). The code is:

Sub Testme()
    Dim rng1 As Range
Set rng1 = Columns(2)
With rng1
    .AutoFilter 1, "0"
    With rng1.Offset
         .ClearContents
         .ClearComments
    End With
End With
End Sub

This list:

6.28    91.4
6.28    95.5
6.28    96.2
6.28    86.9
6.28    0
6.28    100.7
6.28    90.1

Becomes:

6.28    
6.28    95.5
6.28    96.2
6.28    86.9
6.28    
6.28    100.7
6.28    90.1

Any idea how to stop it from removing the first row that doesn't contain zeros is appreciated.

Upvotes: 1

Views: 3160

Answers (2)

chuff
chuff

Reputation: 5866

Your definition of rng1 is selecting the whole column. So, when you do the offset for the clear, you are erasing all of the filtered column C.

This code will do what you want:

Sub Testme()
    Dim rng1 As Range
    Set rng1 = Sheet1.UsedRange 
    With rng1
       .AutoFilter 1, "0"
        With rng1.Offset(1, 0)
            .ClearContents
            .ClearComments
        End With
    End With
End Sub

Upvotes: 0

sous2817
sous2817

Reputation: 3960

No need to autofilter. You can do a find and replace:

Columns(2).Replace What:="0", Replacement:="", LookAt:=xlWhole

Upvotes: 1

Related Questions