Auto Hide Rows If 0 BUT Display When It Changes-VBA

[Xiangwu]

Hi, All,

I have a table that 20 items can change according to different conditions (years or quantity), but each year may not have all 20 items (Quantity might be 0). My ultimate goal is to generate a summary automatically from this table, but I don't know how to do so. I am thinking to repeat all 20 items for each year, and when the value is 0, I will hide the rows. The following is the code I developed.

[COLOR=#00008B][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#000000][FONT=Consolas] HideRows[/FONT][/COLOR][COLOR=#000000][FONT=Consolas]()[/FONT][/COLOR]
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit;">     [COLOR=#00008B]Dim[/COLOR] cell [COLOR=#00008B]As[/COLOR] Range    
[COLOR=#00008B]    For[/COLOR] [COLOR=#00008B]Each[/COLOR] cell [COLOR=#00008B]In[/COLOR] Range([COLOR=#800000]"C:C"[/COLOR])        
[COLOR=#00008B]        If[/COLOR] [COLOR=#00008B]Not[/COLOR] isEmpty(cell) [COLOR=#00008B]Then[/COLOR]            
[COLOR=#00008B]            If[/COLOR] cell.Value = [COLOR=#800000]0[/COLOR] [COLOR=#00008B]Then[/COLOR]                 
                cell.EntireRow.Hidden = [COLOR=#800000]True[/COLOR]            
[COLOR=#00008B]            End[/COLOR] [COLOR=#00008B]If[/COLOR]        
[COLOR=#00008B]        End[/COLOR] [COLOR=#00008B]If[/COLOR]    
[COLOR=#00008B]      Next[/COLOR] 
</code>[COLOR=#00008B][FONT=Consolas]  End [/FONT][/COLOR][COLOR=#00008B][FONT=Consolas]Sub
[/FONT][/COLOR]

This works fine to hide the 0 value rows, but when the row is no longer 0, it's hidden. How can I adjust when the value changes, it will unhide the rows?

Many thanks in advance,

Xiangwu

 

[mumps]

Try:

Sub HideRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim cell As Range
    ActiveSheet.UsedRange.Rows.EntireRow.Hidden = False
    For Each cell In Range("C2:C" & LastRow)
        If cell.Value = 0 Then
            cell.EntireRow.Hidden = True
        End If
    Next cell
    Application.ScreenUpdating = True
End Sub

If the rows are hidden, how do you change the values in column C from zero to something else?

 

[Xiangwu]

Try:

Sub HideRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim cell As Range
    ActiveSheet.UsedRange.Rows.EntireRow.Hidden = False
    For Each cell In Range("C2:C" & LastRow)
        If cell.Value = 0 Then
            cell.EntireRow.Hidden = True
        End If
    Next cell
    Application.ScreenUpdating = True
End Sub

If the rows are hidden, how do you change the values in column C from zero to something else?

All the row values link to some other cells. Consider the C row as a Total Cost (=Unit Cost * Quantities). I have years from 2013-2022. Each year I have different quantities and unit cost, so the cells vary according to the years.

I am trying the code now.

Thank you.

 

[Xiangwu]

Try:

Sub HideRows()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim cell As Range
    ActiveSheet.UsedRange.Rows.EntireRow.Hidden = False
    For Each cell In Range("C2:C" & LastRow)
        If cell.Value = 0 Then
            cell.EntireRow.Hidden = True
        End If
    Next cell
    Application.ScreenUpdating = True
End Sub

If the rows are hidden, how do you change the values in column C from zero to something else?

It works perfect, and I put a button on it. After I have done the calculation, I will click the button to get the summary.

Thanks a lot.