Please help with hiding 0s

[eddysmithjr]

I need some helping hiding 0s in column A on a workbook.

Here is what I have, sheet 1 is a form with questions that can be answered either Yes, No, or NA.

Sheet two has A3:A49, with each cell having an =IF formula in it. Example:

=IF(Sheet1!M55="No",(Sheet2!B55),("0"))

The reason being, if the question in Sheet1!B55 is answered No (which will appear in Sheet1!M55), I need that question that was answered No, to populate in another spreadsheet for stat tracking purposes.

However, in the instance that M55 (as seen above) is answered anything but No, it auto populates in the column A on Sheet!2A49 as a "0".

This repeats over and over, so I may have 20 questions, and 30 zeroes.

I need one of two things, either a one-click macro that will hide all of the 0s, or a macro that will hide and unhide the cells with a 0 in them as necessary, so that all of the questions that populate can be in order, instead of being seperated by a lot of zeroes, I would even be happy if the macro deleted the rows with the zeroes in them if that would be easier.

Two things holding me back:

1-I'm not VBA savy at all, and can pretty much only copy and paste for right now.

2-The "0" I want to hide is not really a zero, it is the product of a formula (=IF(Sheet1!M55="No",(Sheet2!B55),("0")), that just happens to be zero, so I don't know if this will hurt progress or not?

Please help! Again, I need help deleting the cells containing products of "IF" formulas in the rang A#:A49 that result in "0," so that the ones not resulting in "0" can be the only ones left, and not seperate by a row with a 0 in it.


thank you a million times over to anyonet hat can help! I'll be sure to give feed back,

And I'm running office 2007.

Thanks again!

 

[Smitty]

Welcome to the Board!

I'm not clear on why you need to use a text 0, instead of just 0? But for hiding them why not use AutoFilter? Just uncheck 0's.

HTH,

 

[eddysmithjr]

Im not sure of the difference between 0 and text 0?? And its for a business i work for, thats why im trying to avoid the filter .


Thanks for the warm welcome!

 

[Smitty]

Im not sure of the difference between 0 and text 0??

With the text "0" you won't be able to perform calculations on it. You also can't hide them if you go into options and uncheck the show cells with zero values options. It's never a really good idea in a spreadsheet to input any number as text, unless you're dealing with special numbers, like phone numbers, etc.

Since it's such a relatively small range I suppose you could use something like this:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> foo()<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>        Cells.EntireRow.Hidden = <SPAN style="color:#00007F">False</SPAN><br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range([A1], Cells(Rows.Count, "A").End(xlUp))<br>            <SPAN style="color:#00007F">If</SPAN> c.Value = 0 <SPAN style="color:#00007F">Then</SPAN> c.EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[eddysmithjr]

So far so good Smitty! Again, thanks a million.

I just had a couple more questions, I'm still not understanding the difference between zero and text zero?

And is there any way to make the macro hide/delete cells without a value in them? So that if the cell in column A returns as either a zero or nothing it can be hidden or deleted?

And is there any way I can have this Macro run automatically, or create a button that will run this macro?

Lastly, if I want to begin teaching myself VBA, where would be a good place to start from the bottom up?

 

[Smitty]

I just had a couple more questions, I'm still not understanding the difference between zero and text zero?

On a blank worksheet, put 0 in A1, and "0" in A2. Then copy this formula in B1:B2 =A1+1. What result do you get? That's what happens when you make a value into text you lose the ability to work with it as a value.

And is there any way to make the macro hide/delete cells without a value in them? So that if the cell in column A returns as either a zero or nothing it can be hidden or deleted?

Add a qualifier for blank cells:

If c.Value = 0 Or LenB(c.Value) = 0 Then c.EntireRow.Hidden = True

And is there any way I can have this Macro run automatically, or create a button that will run this macro?

Sure you can use a Change event if you want the code to run as the result of a manual entry, or a Calculate event if it's the result of a formula. Just let me know either way, and which cell you want to trigger the event.

Lastly, if I want to begin teaching myself VBA, where would be a good place to start from the bottom up?

Youll find plently of free stuff online. Just do a search for "Free Excel VBA Tutorials". And frankly the best way to start learning VBA is to start playing with the Macro Recorder. Do things that you do normally, so you're familiar with them, then read back the code and you'll start understanding how it relates to what you were doing.