VBA in excel not rebuilding

Post Reply
pablo
Posts: 19
Joined: Mon Jul 16, 2012 11:50 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007

VBA in excel not rebuilding

Post by pablo »

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
Attachments
VB.docx
VB code to hiderows
(10.99 KiB) Downloaded 298 times
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: VBA in excel not rebuilding

Post by Marcus Scherer »

your code works fine for me in Excel 2007 with automatic calculation turned on, e.g. for retrieving values from another workbook on opening.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: VBA in excel not rebuilding

Post by Wim Gielis »

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.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Post Reply