TM1 Forum

Posted: **Thu Aug 30, 2012 9:14 am**

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

Posted: **Fri Aug 31, 2012 5:49 am**

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

Posted: **Sat Sep 01, 2012 12:00 pm**

Somewhat condensed notation:

Code: Select all

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.

TM1 Forum

VBA in excel not rebuilding

VBA in excel not rebuilding

Re: VBA in excel not rebuilding

Re: VBA in excel not rebuilding