Format Cells To Show Dash For Zero

[sujittalukder]

In excel if you press F5\Special, you will be given to select cells with some criteria but there is no criteria to select cells with zero value in a selected range. Is any such option is available in excel or a macro is needed for the same?

[Dave Hawley]

Why not AutoFilter to show only zeros then select visible cells. Or, if copying simply select all rows and copy. By default only non-filtered cells are copied.

[sujittalukder]

Using Auto filter option will not serve the purpose as I have an addin which converts a number into special number format which I want display in "dash" format Also the data are scattered through out the sheets. Using Auto filter will make the task tedious since it will equal to selecting a particular cell with zero value and then clicking "," to convert 0 to -.

[Dave Hawley]

What is you are trying to do exactly? Seems to me all you need is standard Excel number format to show 0 as -

[sujittalukder]

Actually I am using the following number format code

Public Sub IndianNumberStyle() 
    Dim cell As Range 
    Dim mFormat As String 
    For Each cell In Selection 
        If cell.Value > 0 Then 
            mFormat = "[>=10000000]#\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0" 
        ElseIf cell.Value < 0 Then 
            If cell.Value >= -99999 Then 
                mFormat = "(#,##0);(#,##0)" 
            Else 
                mFormat = "[<=-10000000](#\,##\,##\,##0);[<=-100000](##\,##\,##0);(##,##0)" 
            End If 
        End If 
        cell.NumberFormat = mFormat 
    Next cell 
     
End Sub 

THis when applied to the numbers changes - to 0 whereas I want that 0 shall be displayed as -

[rbrhodes]

Hi,

Add a line to convert the zero?

If cell.Value > 0 Then 
    mFormat = "[>=10000000]#\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0" 
ElseIf cell.Value < 0 Then 
    If cell.Value >= -99999 Then 
        mFormat = "(#,##0);(#,##0)" 
    Else 
        mFormat = "[<=-10000000](#\,##\,##\,##0);[<=-100000](##\,##\,##0);(##,##0)" 
    End If 
     'add for zero value
ElseIf cell.Value = 0 Then 
    cell = "-" 
    cell.HorizontalAlignment = xlRight 
End If 

[Dave Hawley]

Why not the standard Excel accounting format so the cell stays as a number? The code for the NumberFormat would be;

_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-

[sujittalukder]

Finally I have rectified the code as below:

Public Sub IndianNumberStyle() 
    Dim cell As Range 
    Dim mFormat As String 
    For Each cell In Selection 
        If cell.Value > 0 Then 
            mFormat = "[>=10000000]#\,##\,##\,##0;[>=100000]##\,##\,##0;##,##0" 
        ElseIf cell.Value < 0 Then 
            If cell.Value >= -99999 Then 
                mFormat = "(#,##0);(#,##0)" 
            Else 
                mFormat = "[<=-10000000](#\,##\,##\,##0);[<=-100000](##\,##\,##0);(##,##0)" 
            End If 
        End If 
        If cell.Value = 0 Then 
            cell = "-" 
            cell.HorizontalAlignment = xlRight 
        End If 
        cell.NumberFormat = mFormat 
    Next cell 
End Sub 

Is this allright?

[Dave Hawley]

....

Why not the standard Excel accounting format so the cell stays as a number? The code for the NumberFormat would be;

_-* #,##0_-;-* #,##0_-;_-* "-"_-;_-@_-

[sujittalukder]

I cant use standard Excel accounting format as the comma situation differs from my code. change comes from umbers having six or more digits.