asked on

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

ASKER CERTIFIED SOLUTION

krishnakrkc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Carlos Ramirez

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.

Andreas Hermle

ASKER

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

Andreas Hermle

ASKER

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

Patrick Matthews

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

Andreas Hermle

ASKER

Hi Patrick:

I mean 'clear' the cell

Thank you, Regards, Andreas

krishnakrkc

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