VBA change event hide and unhide column

I need help with VBA that is automatic "like worksheet change event" that if all of the rows for column result in zero then hide the column. otherwise unhide all column where one or more rows has value other than zero.

I have attached example file.
LVL 6
FloraAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
There's no attachment.

You need to select the file then click Submit to complete the attachment
0
FloraAuthor Commented:
oh, my mistake.  attachment is now attached.

as it can be seen column E and Column K all rows for these 0,  so these two columns to be hidden and when they have value again then it should be un-hidden.


thanks.
Book1.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You may try something like this...
The following code will run on ActiveSheet.
Sub HideUnhideColumns()
Dim lc As Long, lr As Long, i As Long
Dim rng As Range
lr = Cells(Rows.Count, 2).End(xlUp).Row
lc = Cells(1, Columns.Count).End(xlToLeft).Column
Columns.Hidden = False
For i = lc To 3 Step -1
    Set rng = Range(Cells(2, i), Cells(lr, i))
    Columns(i).Hidden = Application.Max(rng) = 0
Next i
End Sub

Open in new window

HideUnhideColumns.xlsm
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

FloraAuthor Commented:
Thanks Neeraj.  but what i wanted was automatic via the worksheet change event. i am not that good to transform this with the worksheet change event. Can you plz help?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Since the values on Sheet1 are derived by formulas, change event will not work on Sheet1.
Instead you need Calculate Event so when you add/remove values in column C on Sheet2, the code to hide and unhide columns on Sheet1 will be triggered automatically and hide and unhide the columns on Sheet1 accordingly.

All you need is to place the following code on Sheet1 Module...
Private Sub Worksheet_Calculate()
Call HideColumns
End Sub

Open in new window

And following code on a Standard Module like Module1.
Sub HideColumns()
Dim ws As Worksheet
Dim lc As Long, lr As Long, i As Long
Dim rng As Range
Set ws = Sheets("Sheet1")
lr = ws.Cells(Rows.Count, 2).End(xlUp).Row
lc = ws.Cells(1, Columns.Count).End(xlToLeft).Column
Columns.Hidden = False
For i = lc To 3 Step -1
    Set rng = ws.Range(ws.Cells(2, i), ws.Cells(lr, i))
    ws.Columns(i).Hidden = Application.Max(rng) = 0
Next i
End Sub

Open in new window

To test the code, add a value in C4 on Sheet2 for the year 2020 and account F156 and as a result, column K will be hidden but column E will be visible as the formula in column E will not return 0 then. And if you delete the value from C4 on Sheet2, column E will be hidden again.
I hope this is what you are trying to achieve.
HideUnhideColumns.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Try this. Make a change to any cell in the sheet and the code is triggered.
HideUnhideColumns.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@Roy
You placed the code for Selection Change Event not for Change Event and code will be triggered by changing the selection on the sheet and I don't think that's required.
As I said earlier, the Calculate Event is the best approach for this scenario as when values are changed on Sheet2, the formulas on Sheet1 will be recalculated and that should trigger the code to hide and unhide columns on Sheet1 automatically. Also, since there are formulas on Sheet1, changing the cell contents doesn't make much sense here.
0
Roy CoxGroup Finance ManagerCommented:
I misread, but I agree calculate would be better

I've edited the code
HideUnhideColumns.xlsm
0
FloraAuthor Commented:
Thanks to both of you.

Neeraj's code nailed it.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome Flora! Glad it worked as desired.
Thanks for the feedback.
0
Roy CoxGroup Finance ManagerCommented:
Did my code not work in the last example?
0
FloraAuthor Commented:
Roy,

Thanks a lot. your code works too. but i used Neeraj's code.
0
Roy CoxGroup Finance ManagerCommented:
I wondered if it hadn't worked for some reason. Thanks for getting back.
0
FloraAuthor Commented:
Thanks Roy.

I also marked your answer as helpful.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.