PDA

View Full Version : [SOLVED] Delete zeros



leovilla64
08-24-2017, 07:10 AM
Friends, I need some help, in the columns of A1 until F1 there are several numbers between them, they say ZERO, I need that from A2, only numbers greater than ZERO.

A1 B1 C1 D1 E1 F1
15 16 0 0 17 0

A2 B2 C2 D2 E2 F2
15 16 17

Grateful...

Kenneth Hobs
08-24-2017, 08:12 AM
Put code into a module. Enter the formula array as explained in comment.


'rRange = single row of cells, return value as a row formula array.
'e.g. select a2:f2, enter formula: =NoZeros(a1:f1), ctrl+shift+enter.
Function NoZeros(rRange As Range) As Variant
Dim i As Integer, j As Integer, a() As Variant
ReDim a(1 To rRange.Count)

j = 0
For i = 1 To rRange.Count
a(i) = ""
If IsNumeric(rRange(i)) And rRange(, i).Value2 <> 0 Then
j = j + 1
a(j) = rRange(, i)
End If
Next i

NoZeros = a()
End Function

leovilla64
08-24-2017, 09:50 AM
Kenneth Hobs, perfect the code, thank you very much for your attention.
Leandro