Vba help

[Isabella]

Hi, i need a code that will remove 0.00 in row 6, my data is layed out like this in row 6

<TABLE style="WIDTH: 288pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=384 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=6 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17>Total</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>0.00</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Total</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>0.00</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Total</TD><TD class=xl23 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>0.00</TD></TR></TBODY></TABLE>

 

[Smitty]

Why not just goto Tools-->Options-->View-->Uncheck Show Zero values?

 

[Isabella]

Why not just goto Tools-->Options-->View-->Uncheck Show Zero values?

If i uncheck zero then it will remove all the zeros from my data, i only want zeros removed from row 6

 

[Mark F]

How about, Highlighting row 6 and formyat cells, number, Custom, replace "General" with a semicolon

;

This should show zeros as blank

Not sure whether that will work for you?

Mark

 

[Isabella]

ADVERTISEMENT

How about, Highlighting row 6 and formyat cells, number, Custom, replace "General" with a semicolon

This should show zeros as blank

Not sure whether that will work for you?

Mark

since i have a VBA code that exports data, i will incoporate this into the procedure, i just ran a macro recorder to record the steps, can the be simplified?


Sub Macro1()
'
' Macro1 Macro


Rows("6:6").Select
Selection.NumberFormat = ";;"
Range("A6").Select
End Sub

 

[Smitty]

Rows("6:6").NumberFormat = ";;"

HTH,

 

[Mark F]

Hi

Just realised that the Number Format I suggested is not exactly correct! I think you what you actually need is

0.00;-0.00;

with the last semicolon making Only Nils show as blank

The first is format for positive numbers, second is for negative numbers, third is for nils

You may have found already that

;;

will show no numbers at all

Mark

 

[Legacy 68668]

If it is not populated by a formula then try

Dim x As String
With ActiveSheet
    With Intersect(.UsedRange, .Rows(6))
         x = .Address
        .Value = Evaluate("if(" & x & "<>"""",if(" & x & "=0,""""," & x & "),"""")"
    End With
End With