Hiding/Unhiding Rows

Hi all,

I’ve created a budget where I’m comparing actual numbers to budgeted numbers. I want to hide rows if the cell reveals a zero value. These cells are all formulae based (drawing data from another worksheet).

So for example, if the formulae in column O and P results in a value of 0, I want that corresponding row to be hidden (and unhidden if not equal to 0). Any ideas on how I can do this?

And I don't want blank rows hidden, just ones with a 0 value.

Thanks for any input,

Marcheese,

Can you post an example for us to see or work on, even with dummy data in it will be fine. Are there forumla in both columns O and P? Should the row be hidden if either gives an answer of 0 or only if both return 0?

Mowgli

Sure, here you go, I hope this is what you're asking for... I'm not sure what the best formatting is for posting questions


Annual Budget Annual Actual
SALES
Material/Parts Sales 4,400,166 4,180,158
Labour Sales 8,590,648 8,161,116
Equipment Sales (1,600) (1,520)
Subcontractors Sales 4,621,125 4,390,069
Freight Sales - -
Warranty Sales 450,563 428,035
Miscellaneous Sales 749,858 712,365
Maintenance Contract Sales - -
Other - -

TOTAL SALES 18,810,761 17,870,223
TOTAL SALES VARIANCE

COST OF GOODS SOLD
Material/Parts 2,520,919 2,394,873
Labour 3,422,545 3,251,418
Employee Benefits 1,220,088 1,159,084
Group Insurance - -


Columns O and P (Budget and Actual) are both formula drawing information from another worksheet. So for example, based on the info above, I'd like Freight Sales, Maintenance Contract Sales, Other and Group Insurance to be the only rows hidden. I still would like the blank row above TOTAL SALES and COST OF GOODS SOLD to exist. This is why an auto-filter does not work.

Marcheese,

If you post a workbook as an attachment it would be easier as the text does not translate across into the correct columns etc. The Rows can be hidden based on a cell value in Column O being 0 or in Column P or both, which would you prefer?

Also, do you want it to automatically update every time a change is made to the sheet or do you want the macro to be run manually whenever you decide just?

I can post the workbook in about an hour if still necessary. I would prefer if both columns had a 0 value to be hidden. If one has a value not equal to 0, I want the row to remain unhidden.

And automatically changing would be much preferred to manual.

The workbook may help. I should be able to get a look at it in the morning if that is not too late? In the meantime, the following may be of use, although it needs to be run manually it seems to carry out the functions you required. To make it simpler I added the word END in the A column after the end of your data as a trigger for when to stop the loop

Sub HideRows()
    ScreenUpdating = False
' Unhide all rows to start
    Rows("1:20").Select
    Selection.EntireRow.Hidden = False
' Section to hide rows if Columns O and P show 0
    Range("O1").Select
    Do Until ActiveCell.Offset(0, -14).Value = "END"
        If ActiveCell.Value = "0" And ActiveCell.Offset(0, 1).Value = "0" Then
        ActiveCell.EntireRow.Hidden = True
        ActiveCell.Offset(1, 0).Select
        Else: ActiveCell.Offset(1, 0).Select
        End If
    Loop
End Sub

Ok I've attached. And anytime you get around to looking at it would be great, there is no rush.

Thanks for the code, I'll try it shortly and let you know how it goes.

Thanks again!

The code works very well thank you! I currently have it assigned to a button, so if you ever manage to figure out how to run this macro automatically, that would be amazing!

Thanks again!

Hi Marcheese,

Try placing the following code in the ThisWorkbook section. It will run automatically any time there is a change made on your sheet is the only potential problem. How do you update the values from the other workbook? Are the links initially updated when you open Bud vs Act or do you manually update them? Think this is the bit that gives me a problem making it run automatically but hopefully the following will work ok for you.

I also removed the "END" from column A so it now stops when it gets to the row saying "Group Insurance" in Column B however you can change this if your sheet changes at all

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    ScreenUpdating = False
' Unhide all rows to start
    Rows("1:20").Select
    Selection.EntireRow.Hidden = False
' Section to hide rows if Columns O and P show 0
    Range("O3").Select
    Do Until ActiveCell.Offset(-1, -13).Value = "Group Insurance"
        If ActiveCell.Value = "0" And ActiveCell.Offset(0, 1).Value = "0" Then
        ActiveCell.EntireRow.Hidden = True
        ActiveCell.Offset(1, 0).Select
        Else: ActiveCell.Offset(1, 0).Select
        End If
    Loop
End Sub

Mowgli

Okay, I’ll try and explain the spreadsheet as best as possible.

There are about 12-15 tabs in my workbook. There are about 7 departments within this business, so the summary sheet (Bud vs Act) where I need the macro works like this… Whatever department is selected from the drop down (e.g. Construction, Service, etc.) then all the data from the budget and actual tabs for those respective tabs will be pulled. So if I select the construction department, the budgeted and actual numbers are pulled from those tabs, and displayed in the summary worksheet. Since not every department has the same source of revenue or cost of sales and has different line items, this is why I need to hide certain rows. For example, construction may have warranty sales, while the administration department will not, so when I select administration, I want the warranty sales row to be hidden.

In the Bud vs Act summary page, the values are updated with any change made to their respective tabs, or when a different department is selected from the drop down menu.

Hopefully this all makes sense, and once again, I will try out your new code and see how it goes! However, since there are many tabs, I don’t know if I should be putting it in the ThisWorkbook section, because I don’t want the macro to apply to all my tabs, but I’ll let you know how it goes!

Thanks again!

Place the code in the relevant sheet section (Bud vs Act) instead and change the start from

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

To

Private Sub Worksheet_Change(ByVal Target As Range)

This will aplly it only when a change is made within the summary sheet

Don't forget, if you are happy with the solution please mark the thread as SOLVED

Works perfectly! Thanks for being so kind and helpful! Very much appreciated