Macro: Hide/Show rows based on cell values

[Hearn2]

Hello everyone.

I am quite new to this aspect of Excel and I have been surfing for quite some time on Google and on this forum, on the subject.
My problem is that I can't find topic that is of use to me, with my level of knowledge on this field.

My problem, is to me, quite simpel:

I would like to create a Show/Hide macro in Excel, place it in the top of my project-budget document, for users to be able to show/hide all the different posts within the budget that are not being used/has a value of 0. So if, for example, I do not fill out the post: "Translator" with any digits, the sub total and sum of that row will be 0 and it would be hidden by pressing the macro.
Since I use this budget for severel different projects, it has gotten quite big and is not very easy to navigate in and this feature would make my life a lot easier.

I have seen this in another document, but I do not have that or the coding from it.


I use Excel 2011 on Mac.
The first rows the macro should apply to would be: 10-33 and it should be hidden based on the value in H10-H33, which is a formula calculated by manually putting in amount, quantity and price as in any budget. Then it skips to row 33-74, due to a new headline for that given area of the budget.


I am truely greatful for you taking your time to help me.

Kind regards,

Henrik Arnrød

 

[saltkev]

Hi

Try this on a copy of your sheet. I understand that the basics of VBA on 2011 are compatable on a MAC but this may not work. Worth gining it a whirle.

Application.ScreenUpdating = False
Dim FX, WSR
Set WSR = Sheets("MyWorkSheetName").Range("H10:H74") ' **** Change to Reflect The Name of Your Sheet & Your Range ****

For Each FX In WSR
        If FX.Value > "" And FX.Value = 0 Then
            FX.EntireRow.Hidden = True
        End If
Next FX

Regards


Kev

 

[Hearn2]

Thank you so much for your reply. Unfortunately it does not quite work as hoped.
I changed the sheet name into Budget and the range from H10 to H29.
So now it looks like:

'HideShowBottom

Application.ScreenUpdating = False
Dim FX, WSR
Set WSR = Sheets("Budget").Range("H10:H29")

For Each FX In WSR
If FX.Value > "" And FX.Value = 0 Then
FX.EntireRow.Hidden = True
End If
Next FX

I get the following error, when running the macro.

Compile Error:

Invalid outside procedure

In the code window it marks "False" in the line reading: Application.ScreenUpdating = False


Kind regards,

Henrik Arnrød

 

[saltkev]

Hi

The Code works fine on a PC, I guess it is not as compatable with a MAC as I had hoped, sorry I can't help further, MAC's are not my bag. If you do not get any further joy from this forum, maybe a forum more MAC orientated.

regards

Kev

 

[Hearn2]

Hey Kev.

Thank you.

Do you reckon it would work if I programmed the macro and the budget on a Windows computer and then afterwards use the file on a mac?

Regards,

Henrik Arnrød