• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Hide

M

mgris

New Member
Hi everyone,


I'm here for my daily dose of help :)


This is what I would like to do :


If a cell of a specific row return a certain result

Then

Hide all the following columns


For instance:

If a cell in row 1 returns 1000 (lets in Z1)

Then

Hide all the following columns (AA, AB, AC etc...)


How in the world would you do that ?


Thx in advance,


M.
 
Something like this?


Sub HideColumns()

Dim c As Range

Dim x As Long

Dim HideYet As Boolean

'Set criteria

x = 1000


Application.ScreenUpdating = False

HideYet = False

For Each c In Range("1:1")

c.EntireColumn.Hidden = HideYet

If HideYet = False Then

If c.Value = x Then

HideYet = True

End If

End If


Next

End Sub
 
Yeah !!! It works... I don't have a clue how it does... But it works ! (I have to learn vb !!!)


Now, I will have to try to customize it to my needs :)


Thx Luke.
 
Your very welcome.


To try and explain, here's a commented version of the macro:


Sub HideColumns()

'Setup definitions of the variables I'm going to use

Dim c As Range

Dim x As Long

Dim HideYet As Boolean


'Set criteria

x = 1000


'Don't want the screen to flash everytime I do something, so turn this off

Application.ScreenUpdating = False


'Set the initial value for my Boolean (True/False) check

HideYet = False


'Look at each cell in the range I specify

For Each c In Range("1:1")


'I'm setting the "hidden" value. My boolean value is initialy False, so columns

'are not being hidden. Once I find criteria value, boolean is switch to true

'and columns will become hidden

c.EntireColumn.Hidden = HideYet


'If I haven't found crtieria cell cell, my boolean is still false so...

If HideYet = False Then


'...I check to see if I've found it yet. Otherwise, this step gets skipped

If c.Value = x Then


'Once it's found, boolean get switched to true, and code proceeds

'to hide all remaining columns

HideYet = True

End If

End If


Next

End Sub
 
You're welcome! Do you still need help making it dynamic/self adjusting, or did my explanation solve that?
 
Hi Luke,


Back from few days off without computers...


Actually, you're help would still be most welcome.

What you suggested needs to be ran manually...

Could you think of something that would auto-adjust the worksheet ?


Thx,


M.
 
mgris


I have taken Lukes last code (Thanx Luke) and removed the comments and placed it inside a Worksheet_Change event

I made a few small simplifications along the way


To use Copy and paste into a Worksheet Module for the worksheet you want it to apply to in VBA

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Dim x As Long
Dim HideYet As Boolean
If Target.Row = 1 Then 'Added this check
Application.ScreenUpdating = False

x = 1000
HideYet = False

For Each c In Range("1:1")
c.EntireColumn.Hidden = HideYet
If HideYet = False And c.Value = x Then HideYet = True
Next
End If 'The check finishes here
End Sub
[/pre]
 
Hi, this is great! Is there a way to instead of hiding from 1000 until the end of the worksheet, just automatically hide some columns and rows, based on the value of a specific cell?


For example, I want all the rows that contain the letter "X" in the A column to be hidden, otherwise they should be shown. And the same for columns; if B1 = "X" then hide, otherwise show.


This would be fantastic for templates that you create with a lot of lines/columns just in case we have more variables in the future, but we end up having a lot of needless space occupied.


Thanks!
 
Yes, This can be done but a query first

If you have a list of things in Col A and when lets say A5 = X you want to hide Row 5

If You Hide Row 5, and have used the Worksheet_Change event, the event of unhiding it will cause it to be re-hidden


So under what basis do you want to hide and unhide things?

How and when do you want such a code to be executed?


Please explain more about what and how you want to do this
 
Sorry Hui, I have been erally busy these last days so I only had the chance to come back to the forum today.


I am thinking about a "dynamic" table showing the names of the customers in C2:IV2(?) and the product clusters in B3:B1000(?)

With a function I would then determine that if the row has no product cluster info (blank cell), then on clumn A there would be an "X". The same way, if in a column there is no customer name (blank cell) then on row 1 there would be an "X".

NOTE: the list of customer names and product clusters would come from another sheet (a sort of a database).


Example:

There are product clusters in B3:B10 and B12:B20. Then A11 and A21:A65536 would be marked "X" (yes, I still have Excel 2003). In this case the correspondent rows would be automatically hidden. I would not want to manually unhide any of them - if I want to see them I would have to go to the database sheet and enter some value in the correspondent slot.

file - http://www.mediafire.com/?84hze1cj9rwm7vu


With this file I would have a dynamic template that would fit all the ranges.


Thanks again for having a look at this
 
Also, and I forgot to mention, the trigger for that action could either be as soon as "x"s are changed somehow (automatic) or via button or shortcut.


Thanks again
 
You must log in or register to reply here.
Back
Top