Auto Hide/Unhide Row With Zero Value Cell

[js10053]

Hey everyone,

I am hoping you can help me out with creating a way for my spreadsheet to auto hide a row if there is a value of zero and bring it back if the value changes to anything greater than zero. I'm not sure how much information you need, so I will try to give as much detail as possible. I have been researching this for a few days and it seems to me that my best option would be to use a Macro, however, I don't have any knowledge of VBA so while I was finding some that seemed like it would work, my lack of skill in writing it was preventing me from editing it to be able to use how I need.

Unfortunately I cannot post the sheet, but I'm going to try and include as much detail as possible. The sheet itself is used as a template and is used each day by users and saved as a new copy after. There are two sheets that we use, the first sheet is where the original data is input. It is simple formulas to calculate up costs and such. The second sheet pulls the information on items sold so that it can be viewed quickly and provides additional information on what was sold. What I want to do is be able to have the rows hidden when an item is not sold so that we don't have the information there when we do not need it.

The sheet that I need the rows hidden in currently uses column B for the quantity, which currently will show a zero automatically unless the value changes on the first sheet. Currently I have rows 2-38 in use, but I assume I would be able to modify the VBA with this information if needed.

If there is an easier way for me to go about this, I am more than willing to try it as well, it just seemed like this was the best way to go based on what I was reading. I have found several posts on this being done, but like I said, since I can't really write the VBA it wasn't working for me. I think it is fairly simple what I need to do, again, I just need the cells to hide if the value is zero, but unhide if the value is changed, i.e. as a result of a number being input incorrectly on the first sheet. I also considered using a button to apply the macro? I'm not sure if this would make it easier so that the rows are not hidden until everything has been input.

Let me know if there is any information that I have left out and I can add that as well.

Thank you so much for any help you can provide!

 

[tlowry]

Here is one approach:

• Put this code in the module for the sheet containing your test data.
• Change the "B" to the column you wish to check for 0.
• Test and post results.

(run unHide to show all rows)

Const DataCol = [B]"B"[/B]

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    On Local Error Resume Next
    With Columns(DataCol)
        .Replace "0", "#N/A", xlWhole
        .Cells.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Hidden = True
        .Replace "#N/A", "0", xlWhole
    End With
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Sub unHide()
    Cells.EntireRow.Hidden = False
End Sub

 

[js10053]

Thank you for your quick reply. I tried putting the code in but it doesn't seem to be working. I think it may be an error on my part. As I mentioned this is the first time I have done anything like this, so I am not sure if I am doing it right.

I entered the code into a module and pressed the green run button... I thought that was all I needed to do, but do I need to do something else?

Thanks again

 

[tlowry]

More details:

Copy the code as is into the module that is for your data sheet. In my workbook it is titled: Sheet1(Data)

steps to install:

• Alt F11 to get to development editor
• Double click on Sheet1(Data) or what your workbook has for the data
• Copy and paste the code into the module (right side of the screen)
• Change the "B" to the letter of the column that is to be checked for a zero
• Save

To test:

• Change any cell in the "data" sheet
• All the rows with a 0 in the designated column should be hidden

To unhide run "unhide"

We can do this; Let me know what happens!

 

[js10053]

That is what I had been doing, so I was on the right track. When I go to save it gives me an error that says I can't save because I am using a macro free workbook. When I try changing the values, even without saving it will not delete any of the rows.

And I'm not sure if I should ask this now or wait until we get the rest figured out. Is there any way to have the macro run that would be easy for even a user that isn't familiar with excel? Some of the people that use it will be able to make the changes if needed, but most of them are not as experienced as even I am. So it appears the way your code works the rows would auto hide, but they would have to run the unhide macro in order to get the cells back if something changed? Is there any way I can insert a button or something so that they can just have the macro run when they know it is okay to hide the cells? They could easily close the file and start over, but I'm just trying to make it as user friendly as possible.

Thanks again!

 

[tlowry]

You are correct in that Macros must be enabled to do this. There is no way to enable macros via VBA. If there was, then it's not protected from macros. If you enable macros, be sure you have up to date, enabled, virus protection software.

Actually, I didn't think the "any change" in data to hide/unhide was user friendly either. So here's another way of doing the change:

Assign a key sequence to run the macro to do this:

The "+^{RIGHT}" sequence is shift and ctrl and right arrow(as we are incharge, we can change this later)

Install:

• Get rid of all the previous code
• Copy the following into the ThisWorkbook module
• Change the "Const's" values to match your environment
• Save and test

Const DataSheet = "Data"
Const DataCol = "B"
Const KeyToAct = "+^{RIGHT}"
Public Sub HideUnHide()
    If ActiveSheet.Name <> DataSheet Then Exit Sub
    Static BHide As Boolean
    If IsEmpty(BHide) Then BHide = True
    Select Case BHide
        Case False
            Cells.EntireRow.Hidden = False
        Case Else
            Application.ScreenUpdating = False
            On Local Error Resume Next
            With Columns(DataCol)
                .Replace "0", "#N/A", xlWhole
                .Cells.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Hidden = True
                .Replace "#N/A", "0", xlWhole
            End With
            Application.ScreenUpdating = True
    End Select
    BHide = Not BHide
End Sub
Private Sub Workbook_Open()
    Application.OnKey KeyToAct, "Thisworkbook.HideUnHide"
    MsgBox "Keys " & Chr(34) & KeyToAct & Chr(34) & vbCrLf & _
            " enabled to hide/unhide rows with zero."
End Sub

 

[tlowry]

I forgot one step in the install after the save, close, and then open the excell workbook. This is when the "hot keys" get assigned to the macro.

 

[js10053]

Okay, so I went ahead and tried it with the new code. When I went to save it, even with enabling Macros, I still got the error message again. But I went ahead and saved it, closed it and restarted. When I entered the code, a prompt came up with the information on how to use the "hot key." But when I try it, it doesn't do anything but move cells. When I go in to look at the code, it is blank.

I was going to go ahead and attach the file to make it easier, as I'm sure it helps to have the actual spreadsheet, but I don't have the right to post attachments yet. If there is any way I can post the file, let me know and I will include that so hopefully it is a little easier. I am sure the issue is on my end since this is all new to me.

Thank you so much for all your help!