Automatically Hide/Unhide Rows with a 0 Value that contain a formula in Cell A

[mikeb7500]

It seems like I've copied and pasted every example I could find, and none have worked. Sheet One, is the Input Sheet, that populates the values in Sheet Two which is the Invoice Sheet(Sheet Two, all Cells are totally locked).

I have 10 devices(rows) I can enter in the input sheet(sheet 1). The next row would be the totals. If I only enter 7 devices(rows), I would like the remaining 3 rows to be hidden(sheet two), like on an invoice. If I need to add an 8th device(row) it will unhide that 8th row on Sheet Two, when I enter the information in the appropriate Cell in the input sheet, etc.

I'm trying to avoid having empty rows between the device list and the totals.

My first Device Column/Row starts at A8, and my last Device Column/Row ends at A26. An example of the formula in Column A is: =IF('Input Sheet'!$B15>0,'Input Sheet'!$B15,""). So if I don't enter anything in the input sheet Cell $B15, the value is 0 and the A8 row hides. Help

 

[NeonRedSharpie]

I think the reason you are having troubles (this is an assumption) is that your worksheet is protected. Have you tried using VBA to unprotect it, and then reprotect it after the function has run?

 

[jasonb75]

To automatically hide rows would require vba.

As you only have a few rows of data, why not float the totals?

=IF('Input Sheet'!$B15>0,'Input Sheet'!$B15,IF('Input Sheet!$B14>0,(totals formula here),""))

 

[mikeb7500]

I think the reason you are having troubles (this is an assumption) is that your worksheet is protected. Have you tried using VBA to unprotect it, and then reprotect it after the function has run?

Yes, I've unprotected it, entered the code in Visual Basic, then reprotected it. I just need someone to direct me on the code please.

 

[mikeb7500]

I've been cutting and pasting code from various searches...I'm just trying to eliminate the unused rows. If I enter only 2 devices, I would like the other 8 rows of devices, not used, hidden.

 

[jasonb75]

Yes, I've unprotected it, entered the code in Visual Basic, then reprotected it. I just need someone to direct me on the code please.

You need to unprotect the sheet while the code runs, not just when you enter the code into the editor.

Also if you want the code to run automatically then you need to look at a worksheet event, not just regular code.

Did you try my suggestion of modified formula to eliminate the need for using vba?

 

[mikeb7500]

Jason, I just watched a video on floating the totals, but it's not what I'm trying to accomplish...but thanks. Below I've pasted part of my spread sheet. There are two rows between Item 8 SIP-T26P and Total Hardware Cost. Each cell is formatted with boxes, that won't show on here. So I have a lot of space between the two and 2 rows of boxes, that have 0 Value, in Column A, and I'd like to hide, compressing the space to display the totals, right under row 8. If I enter a value from Sheet One to make rows 9 and 10 appear, I want them to unhide. This way, if I only enter two items, I don't have 6-8" of blank boxes. Thanks

7 SIP-T28P $179.00 $1,253.00 <tbody> </tbody> 8 SIP-T26P $139.95 $1,119.60 <tbody> </tbody> Total Hardware Cost US $ $5,595.65 <tbody> </tbody>

<tbody>
</tbody>

 

[jasonb75]

Mike

I'm trying to make sense of what you're working with, you say you have up to 10 rows of devices, but that the range they fill is A8:A26, which makes 19 rows. How does that work, do you have empty rows between devices?

 

[mikeb7500]

Sorry for the confusion Jason, yes, and I just took out those blank rows to lessen the confusion. So the rows are now A8 to A17. It says I can't post an attachment, so I'd like to send this workbook with your permission. Although we're not supposed to send emails...I'd still like to get a copy to you, since when I tried pasting to here, it looses all it's details. Whatever the VBA code is, I have 3 additional worksheets I'd like to apply it to, within the same workbook. If you can send me an email, I'll attach my workbook and send it for your review...mikeb7500@gmail.com It would help me complete a long term project. And if anyone else has an answer and would like to see my workbook, I'll do the same...please let me know...thanks!

 

[jasonb75]

No need to email the workbook Mike, try this code, use a copy of your workbook in case of any problems.

Private Sub Worksheet_Activate()
Dim c As Range
    Me.Unprotect Password:="123"
        For Each c In Range("A8:A17")
            c.EntireRow.Hidden = (c.Value = "")
        Next
    Me.Protect Password:="123"
End Sub

Note that this doesn't go into a regular module, i.e. 'Module1", but into the worksheet module for Sheet2. Also I've used 123 as a test password for locking the sheet, you will need to replace this with your real password.

If you do need to provide a copy of your workbook it is preferred to use a file share service such as dropbox, that way if you have several people helping you, everyone will be able to see the latest revision of the workbook.