Excel Help

[asksign]

Hi,

I have an data as mentioned below, If the wanted is zero, then the cell should be empty else it should show the number.

Stock	Available	Wanted
6	5	1
5	5	0

<colgroup><col><col><col></colgroup><tbody>
</tbody>

can any one help me in this.

Thanks in advance.

 

[Logit]

.
First, consider the title to your thread. "Excel Help"

Now, consider that you are someone else doing a search of the Forum for code that will cause a cell to be blank if the cell contains the number zero.

How successful would your search be based on the title of your post ?

Everyone who posts or searches on this and other Excel Forums is seeking "Excel Help" in some form or fashion. Using that as the title of a post does not
provide for a successful search for others. Also consider that your post/s are meant not only to assist you, but also to assist others in the future.


Now, to the heart of your question ... here are two macros. One is driven by a command button and the other is automatic.


Command button driven (paste into a Routine Module) :

Sub ChangeTest()
    Dim LastRow As Long
    Dim i As Long
      LastRow = Range("G" & Rows.Count).End(xlUp).Row
      For i = 2 To LastRow
        Select Case Range("G" & i)
          Case 0
            Range("G" & i) = ""
        End Select
      Next i
End Sub

Automatic (Paste into the worksheet level module) :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 Dim LastRow As Long
    Dim i As Long
      LastRow = Range("G" & Rows.Count).End(xlUp).Row
      For i = 2 To LastRow
        Select Case Range("G" & i)
          Case 0
            Range("G" & i) = ""
        End Select
      Next i
End Sub

 

[asksign]

Hello Sir,

Thanks for your kind advice, I am not so familiar in excel. Is there any simple way to get the results in excel via excel formula, other than Macros.

Please accept my apologies, If my question hurts you.

 

[Logit]

.

To answer your question ... No.


Using formulas :

Let's say the cell where a Zero may appear is F3. Using formulas you can cause any other cell to be blank if there is a zero in F3, but you won't be able to make F3 itself blank.

 

[sandy666]

maybe it will do what you want:
- go to Options
- Advanced
- Display options for this workshet
- uncheck : show a zero in cells that have zero value

 

[jtakw]

Hi,

May be OP simply means this:

Book1
	A	B	C
1	Stock	Available	Wanted
2	6	5	1
3	5	5
Sheet164

Cell Formulas
Range	Formula
C2	=IF(A2-B2,A2-B2,"")

Formula copied down.

 

[asksign]

Hi Jtakw,

Thank you so much, it works.

 

[jtakw]

Hi Jtakw,

Thank you so much, it works.

You're welcome.

 

[MimiM]

Hi, I can see this works, but WHY does it work? On the face of it, it's just a calculation of the difference between the two cells, so what is the IF statement testing for True/False?

 

[RoryA]

For Excel's purposes, any non-zero number can be coerced to True, and 0 to False. So essentially the formula is a shorthand for:

=IF(A2-B2<>0,A2-B2,"")