Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

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
0
Andreas Hermle
Asked:
Andreas Hermle
1 Solution
 
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now