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
VBA in excel not rebuilding
-
- 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
- Attachments
-
- VB.docx
- VB code to hiderows
- (10.99 KiB) Downloaded 300 times
-
- 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
your code works fine for me in Excel 2007 with automatic calculation turned on, e.g. for retrieving values from another workbook on opening.
-
- MVP
- Posts: 3113
- 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
Somewhat condensed notation:
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.
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
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
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