Hide columns base on text

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
815
Hi all, i would to provide me support so that, when i enter in cell "I4" the text "MONTHLY" should hide automatically the entire columns "D" and "E" and when i change the text to "YEARLY" should unhide the columns. Thank you all in advance
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,022
Office Version
365
Platform
Windows
How about
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Address(0, 0) = "I4" Then
      Range("D:E").EntireColumn.Hidden = Target = "MONTHLY"
   End If
End Sub
 

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
815
Hi Fluff, it doesn't work but thank you for your support. The cell "I4" contains by default the text "MONTHLY" and so the code should valid this text / word and base on it should hide the columns "D" & "E" and whenever i change the text to "YEARLY" should unhide the related columns. Do not worry if is a complicate function . I can process by manual method. Thanks once again for your support.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,022
Office Version
365
Platform
Windows
Did you put the code the sheet module where you want to hide the columns?
If so, did you change the value in I4
 

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
815
I copied the code into the VBA module but it doesn't run and then i changed the 1st line "Private Sub Worksheet_Change(ByVal Target As Range)" to "Sub Worksheet" and It stops on the below point:
If Target.CountLarge > 1 Then Exit Sub
The message is: "Run-time error 424"
"Object required"

I'm have to write somewhere the name of the sheet?

Thank you Fluff for your attempt to resolve my project. Hv a great day!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,022
Office Version
365
Platform
Windows
Right click the tab on the sheet you want this to work on, select View code & paste the code into the window that opens up.
Do not change the name of the sub.
 

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
815
Apologies Fluff, i didn't know the way you advised me. Thank u so much. Is very kind of you!
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,022
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Panoos64

Well-known Member
Joined
Mar 1, 2014
Messages
815
I am sorry that i bother you again Fluff, just i wanted to incorporate the above code with another one. I mean before the "End Sub" of a previous command/code, i would like to paste your above code. I attempted but it doesn't work. Apologies for such query. Many thanks in advance
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,022
Office Version
365
Platform
Windows
Would you like to give me some more info?
I do you want me to read your mind? ;)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,650
Messages
5,469,937
Members
406,674
Latest member
MrSTruct

This Week's Hot Topics

Top