# Hide/unhide columns

#### prem1611

##### New Member
Hi,

i have following data

From A3:E15 I have a table with current year data(CYR) and from G3: K15 with CYR Year To Date(YTD) data.</SPAN></SPAN>

In A1 I have “YTD” and in G1 I have “CYR”</SPAN></SPAN>

If I click on “YTD” column A:E should hide and column G:K should appear. </SPAN></SPAN>
If I click on “CYR” column G:K to hide and Column A:E to appear</SPAN></SPAN>

I used the following code but it solves only half the problem</SPAN></SPAN>

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN></SPAN>
If Range("d1").Value = "YTD" Then</SPAN></SPAN>
Columns("B:L").EntireColumn.Hidden = True</SPAN></SPAN>
Else</SPAN></SPAN>
Columns("B:L").EntireColumn.Hidden = False</SPAN></SPAN>
End If</SPAN></SPAN>
End Sub

Any ideas pls</SPAN>

Prem</SPAN>

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Try:
Code:
``````Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = "YTD" Then
Columns("B:L").EntireColumn.Hidden = True
Else
Columns("B:L").EntireColumn.Hidden = False
End If
If Range("D1").Value = "CYR" Then
Columns("G:K").EntireColumn.Hidden = True
Else
Columns("A:E").EntireColumn.Hidden = False
End If
End Sub``````

You are passing in the target as a range, but never using it. Although I can see you are setting the hidden property, I do not see what would trigger the call to the Sub. You might want to use events. Look at:
Code:
``[COLOR=Green]'Declaration[/COLOR] [COLOR=Blue]Event[/COLOR] SelectionChange [COLOR=Blue]As[/COLOR] DocEvents_SelectionChangeEventHandler</pre>``

This code hides all the colums excluding A1

I have used following code,

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("D1").Value = "YTD" Then
Columns("B:L").EntireColumn.Hidden = True
Columns("M:W").EntireColumn.Hidden = False
End If
If Range("N1").Value = "CYR" Then
Columns("M:W").EntireColumn.Hidden = True
Columns("B:L").EntireColumn.Hidden = False
End If
End Sub

but in this code, IF in "D1" i give YTD, column B:L is not hiding and M:W is opening
Whereas if in "N1" i give CYR, column M:W is hiding and B:L is opening

How to change the code to make the first condition work?

Any ideas pls

Replies
16
Views
587
Replies
8
Views
85
Replies
0
Views
221
Replies
3
Views
812
Replies
0
Views
1K

1,203,453
Messages
6,055,530
Members
444,794
Latest member
HSAL

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back