Deletion of zeros (0)

[anthonymedz]

Hi all,

Good day.
Can anyone would help me on how to delete all zeros(0) and put in place the value in one column.

Example:

A B C D E A
9 0 0 0 0 9
0 10 0 0 0 10
0 0 49 0 0 ==result==> 49
0 0 0 400 0 400
0 0 0 0 100 100

 

[Peter_SSs]

Try the following in a copy of your workbook.

Sub RemoveThem()
  With Range("A1").CurrentRegion
    .Replace What:=0, Replacement:="", LookAt:=xlWhole, SearchFormat:=False, ReplaceFormat:=False
    On Error Resume Next
    .SpecialCells(xlBlanks).Delete Shift:=xlToLeft
    On Error GoTo 0
  End With
End Sub

 

[anthonymedz]

This is awesome! I highly appreciate your help! Thank you.

 

[Peter_SSs]

This is awesome! I highly appreciate your help! Thank you.

You are welcome. Glad it helped.

 

[anthonymedz]

Hi Peter,

Good day.
Can you help me again. I would like to have is to Copy the value from the other sheet and paste it in different sheet without overlapping the value on each column. For example I paste the value in column "A1", the 2nd that i would paste is in "B1" or "C1"......."Z1" without overlapping the value in each Column "A1" to "Z1".

Thank you for the advance help.

 

[Peter_SSs]

If this is a different question, then you would be best to start a new thread.

In any case I do not understand what you are asking. So,

- if a new question then start a new thread and explain more fully & clearly, perhaps with examples.

- if this is related to the original question in this thread then you will still need to explain more fully & clearly, perhaps with examples.

 

[Rick Rothstein]

- if this is related to the original question in this thread then you will still need to explain more fully & clearly, perhaps with examples.

I think it is related... it sounds to me like the OP wants you to take the output from your macro (Column A values) and Transpose them into Row 1 on some different sheet.

 

[anthonymedz]

Hi All,

Good day.
I will create new thread with example and will explain more briefly.