VBA code for auto hide/unhide rows

[EffluX]

Hi all,

I've done some searching on different forums and can't seem to achieve my objective, I'm also totally new to VBA.

I would like certain rows in a column that are "0" to auto hide, but when the cells receive new data and become a value I want the row to auto unhide. The cells in the column in question have a function that receive data from another sheet in the workbook. So when the user inputs data in the other sheet it will cause a value to appear where the "0" was.

I have used a small code that I got from a MrExcel post in 2011 but I don't have enough knowledge to edit the code.
So the code does auto hide the "0" but it requires extra code to auto unhide when the cell obtains a value from user input from the other sheet and maybe a trigger to rerun the code automatically, not sure if this is possible.

Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual

For Each c In Range("G7:G200")
If c.Value = 0 Then Rows(c.Row).Hidden = True
Next

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

[code]

Thanks a lot in advance

 

[dmt32]

Hi welcome to forum

You don't need extra code to unhide the rows, you just need to write it slightly differently

Sub HideRows()
    Dim ws As Worksheet
    Dim cell As Range
    
    Set ws = Worksheets("Sheet1")
    For Each cell In ws.Range(Range("G7"), ws.Range("G" & ws.Rows.Count).End(xlUp))
        ws.Rows(cell.Row).Hidden = CBool(cell.Value = 0)
    Next cell
End Sub

Update to your code tests if cell value in range is = to 0 (zero) which will return True (row hidden) otherwise False (row not hidden)
I have also made the range dynamic which will expand or contract as you add or remove data. If this is not required then revert back to your fixed range.

As code is intended to work on a non active sheet, you need to qualify the range to the required worksheet. Change the sheet name shown in red as required.


To call from the user input sheet you mention you can use that sheets Change Event

Right Click Tab > View Code & then insert following code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("H10,H12,H14")) Is Nothing Then HideRows
End Sub

Input ranges shown in red are just an example - change as required

Hope Helpful

Dave

 

[EffluX]

Hi Dave,

Thanks for the quick reply.

I did update the code in the sheet where I want the "0"'s to be hidden but when I change data from the other sheet which causes values to be updated, the rows are not hidden/unhidden automatically, I have to literally go back into the code and run it for the rows to be updated which I do not want to do.
This is what it looks like now.

Sub HideRows()
    Dim ws As Worksheet
    Dim cell As Range
    
    Set ws = Worksheets("BOQ Rising main")
    For Each cell In ws.Range(Range("I39:I124"), ws.Range("I" & ws.Rows.Count).End(xlUp))
        ws.Rows(cell.Row).Hidden = CBool(cell.Value = 0)
    Next cell
End Sub
[code]


Also I used the code for the other sheet but I get a "Compile Error: Sub or function not defined"

[code]
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range(S24:S507"))Is Nothing Then HideRows
End Sub
[code]

 

[dmt32]

Hi,
there are some errors in changes made


Place updated code in STANDARD module

Sub HideRows()
    Dim ws As Worksheet
    Dim cell As Range
    
    Set ws = Worksheets("BOQ Rising main")
    For Each cell In ws.Range(ws.Range("I39"), ws.Range("I" & ws.Rows.Count).End(xlUp))
    ws.Rows(cell.Row).Hidden = CBool(cell.Value = 0)
    Next cell
End Sub

Place following updated event code in your input sheet code page

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("S24:S507")) Is Nothing Then HideRows
End Sub

Dave