VBA in excel not rebuilding

jxs0103

Hi,

I am a real beginner with VB code.

I have a VB code in excel to hide rows where values are 0.

This works 100% untill I close the sheet and re opens it.

I save the sheet as xlsm (macro enabled).

When I open the sheet my vb code to hide the rows does not work. It is still there but it does not rebuild.

Any help is appreciated.

See attached sheet for my code.

I should add refresh or something somewhere I think.

Thank you

wzsycdh

your code works fine for me in Excel 2007 with automatic calculation turned on, e.g. for retrieving values from another workbook on opening.

onelovely

Somewhat condensed notation:

Code: Private Sub Worksheet_Calculate()

    Dim LstRw As Long, Rw As Long
   
    Application.ScreenUpdating = False

    '''Un-hide all rows to start with
    Rows("15:" & LstRw).Hidden = False

    '''Define LstRw as the last row in column F with data.
    LstRw = Cells(Rows.Count, "G").End(xlUp).Row

    ''' Go through column G (starting at row 15) & hide all rows with a value of 0
    For Rw = 15 To LstRw
        If Cells(Rw, "G") = "" Then Rows(Rw).Hidden = True
    Next

    Application.ScreenUpdating = True
   
End Sub

I also do not see why this would not work.
Although in general I dislike the Worksheet_Calculate() event. I would rather go for a Worksheet_Activate() event.

By the way, instead of looping, consider an autofilter / advanced filter. And have a look at SpecialCells too.