Hiding Columns based on formula results

[jhennige]

I searched the site and found several similar questions, but I could not find the exact answer to my problem. I expect that this is an easy one.

I have a formula (similar to [=If(a1>b1,0,1)] in the protected cells F2:Q2 that will return either a 1 or a 0. I need a macro that will hide every column that contains a 0. Of course the macro needs to account for the formula and not just the value.

The data is dynamic so the macro needs to run if there are any changes to the formula results in F2:Q2.

The worksheet is protected with a password.

I am using Excel 2000 and Windows 2000.

Any help would be appreciated.

 

[Richie(UK)]

Hi j,

Is this the sort of thing you want?

Private Sub Worksheet_Calculate()
Dim rng As Range, rngCell As Range

Set rng = Range("F2:Q2")
Me.Unprotect password:="Letmein"
'use your password here
Application.ScreenUpdating = False
rng.EntireColumn.Hidden = False
'show all
For Each rngCell In rng
    If rngCell.Value = 0 Then rngCell.EntireColumn.Hidden = True
    'hide if its a zero
Next
Me.Protect password:="Letmein"
Application.ScreenUpdating = True
Set rng = Nothing
'free memory

End Sub

HTH

(To use, right-click on the sheet tab concerned and select View Code, then paste in the above)

 

[jhennige]

It works great. Thanks.

The only problem I am having is that I have lost the "Undo" functionality even if the range F2:Q2 is not changed. The undo function is important because many of the users are not that comfortable with Excel.

Is there a way to modify the code so that the undo function still works?

 

[Richie(UK)]

Hi j,

To the best of my knowledge, Undo only relates to actions taken by the user through the user interface. It is not possible to have an Undo after a macro has run (*). If this is a big issue then you may need to re-think your approach and have the macro triggered via a button (once the user is sure of their input) rather than on a worksheet event.

* It is possible to undo macros with a bit of effort - see the following link to the JWalk site:
http://www.j-walk.com/ss/excel/tips/tip23.htm
However, this seems a lot of effort and overhead to achieve your relatively simple objective.

 

[J Holmes]

Ritchie
Can you tell me what rng and rngcell are in the programming? I cannot find any reference to it in the help files and books, and am trying to learn by following the logic in the programming.
Thanks

 

[Richie(UK)]

Ritchie
Can you tell me what rng and rngcell are in the programming? I cannot find any reference to it in the help files and books, and am trying to learn by following the logic in the programming.
Thanks

Hi,

You won't find any reference to them in the Help files or books - they are Variable names. You'll notice at the start of the routine there is a line like this:

Dim rng As Range, rngCell As Range

This explicitly declares both rng and rngCell as Range Variables. The names don't really have any significance in the code, they could be Fred and Wilma :wink: , but it makes it easier to follow the code if they have some relevance to the routine or the variable type (or both).

Have a look in the VBE Help files under "Dim Statement" and "Declaring Variables".

The rng variable is then set (using the Set statement) to represent the range of cells "F2:Q2". The variable rngCell is then used to loop through each cell in rng (see, rngCell sort of indicates that this might be the case doesn't it?).

Let me know if you need any further clarification.

 

[J Holmes]

Ritchie

I've got a lot to learn!

Thanks

 

[jhennige]

Thank you for the explanation. Knowing that the undo function does not work after a macro runs helps a lot.

The problem that I'm having with the code provided (thanks again) is that it runs when ANY change to the worksheet occurs. If the code could be written so that it only runs when the relevant cells are changed (F2:Q2--remembering that they contain formulas) then this would be more user friendly (users could undo regular changes but not changes that activate the macro).

Is there a way to do this?

 

[Richie(UK)]

Hi,

You could use the Worksheet_Change event to only look for changes in the cells that influence you formulas, however this will not prevent the UnDo problem as you are still using event macros.

As I suggested above, you may need to consider running a macro from a button or hot-key combination rather than an event macro. (Either that or tell your users to be more careful :wink: )

 

[jhennige]

Richie,

Using the Worksheet_Change event to only look for changes in the cells that influence the formulas is a great idea.

There is one cell (C30) on a separate sheet (say Sheet1) that drives all the formulas.

How would I incorporate the Worksheet_Change code into your original solution?

You've been a great help! Mucho Appreciado!