Delete all zeros in column A using VBA

Dear Experts:

I would like to achieve the following using VBA:

Delete all zeros (0) in column A of the active worksheet regardless of their formatting, i.e. zeros formatted as text or number.

Help is much appreciated.  Thank you very much in advance.

Regards, Andreas
Andreas HermleTeam leaderAsked:
Who is Participating?
 
krishnakrkcCommented:
Hi,

Try

Sub kTest()

    With ActiveSheet.Columns(1)
        .Replace "0", "", 1
        '//if you want to delete the rows uncomment the following 3 line

'        On Error Resume Next
'        .SpecialCells(4).EntireRow.Delete
'        On Error GoTo 0
    End With

End Sub

Kris
0
 
slycoderCommented:
In Excel 2007 and above - you can Hide all zeros in the worksheet with - File -  Options - Advanced - Display Options For This Worksheet -  and uncheck: Show a Zero in Cells that have a Zero Value.
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Kris,

thank you very much for your swift help. I am afraid to tell you that nothing happens when running your code. My requirements may have been a little bit to vague.

The following code which I managed to set up myself does what I want. It is a little bit slow though. And I wonder that all Zeros are deleted even if they are formatted as numbers.

Regards, Andreas


Sub kTestfinal()

Dim cel As Range

For Each cel In ActiveSheet.Columns(1).Cells
If cel.Value = "0" Then
cel.ClearContents
End If
Next cel

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Andreas HermleTeam leaderAuthor Commented:
Hi slycoder:

thank you very much for your feedback. I am aware of this feature, but it is regrettably of no use for my requirements.

Regards, Andreas
0
 
Patrick MatthewsCommented:
Andreas,

Do you mean "clear" the cell, or delete the cell?

If delete, do you mean delete just that cell, or the whole row?

If delete just that cell, do you want to shiftthe cells below it up, or the cells to the right leftward?

Patrick
0
 
Andreas HermleTeam leaderAuthor Commented:
Hi Patrick:

I mean 'clear' the cell

Thank you, Regards, Andreas
0
 
krishnakrkcCommented:
Hi

It works fine for me regardless of their formatting.

Are you able to do this manually ?

Hit Ctrl + H
Find what: 0
Leave 'Replace with' box empty
Replace All
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.