Hide active column based on cell value from its column

mortaz111

New Member
Joined
Apr 6, 2022
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Hi guys I'm new here:biggrin:.

I have a work where I input data in each column, but it is pretty hard to keep clicking Tab button to move to the next column. So, my question is, is there any why to to hide a column where when I input a value in one of 2 cells in the column, the column will be hide automatically.

image12.png
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,470
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops-up:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if updates cell not in cells B6 or B7
    If Intersect(Target, Range("B6:B7")) Is Nothing Then Exit Sub
    
'   Check to see if a value entered
    If Target <> "" Then
'       Hide column B
        Columns("B:B").EntireColumn.Hidden = True
    End If
    
End Sub
As long as you have VBA enabled, this will automatically hide column B if you enter a value in B6 or B7.
 

mortaz111

New Member
Joined
Apr 6, 2022
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
Welcome to the Board!

Right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this VBA code in the VB Editor window that pops-up:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

'   Exit if updates cell not in cells B6 or B7
    If Intersect(Target, Range("B6:B7")) Is Nothing Then Exit Sub
   
'   Check to see if a value entered
    If Target <> "" Then
'       Hide column B
        Columns("B:B").EntireColumn.Hidden = True
    End If
   
End Sub
As long as you have VBA enabled, this will automatically hide column B if you enter a value in B6 or B7.
Thanks you for welcoming me :biggrin:

Thanks it's work, but when I go to column C and input 1 in C6 or C7 it's not hiding the coloumn anymore. Can it work continuously to the next columns?
 

bebo021999

Well-known Member
Joined
Jul 14, 2011
Messages
1,793
Office Version
  1. 2016
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim InputRng As Range
Set InputRng = Range("B6:F7") ' change to actual range
    If Intersect(Target, InputRng) Is Nothing Then Exit Sub
    Target.EntireColumn.Hidden = True
    Target.Offset(0, 1).Select
End Sub
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,470
Office Version
  1. 365
Platform
  1. Windows
Sorry, didn't realize that you wanted it to work for multiple columns. Thought you just wanted column B.
bebo21999's code should do what you want.
 

mortaz111

New Member
Joined
Apr 6, 2022
Messages
3
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim InputRng As Range
Set InputRng = Range("B6:F7") ' change to actual range
    If Intersect(Target, InputRng) Is Nothing Then Exit Sub
    Target.EntireColumn.Hidden = True
    Target.Offset(0, 1).Select
End Sub
Thanks you so much, it works :biggrin:

Sorry, didn't realize that you wanted it to work for multiple columns. Thought you just wanted column B.
bebo21999's code should do what you want.
No problem sir, I make a wrong question. Thanks you for your answer :biggrin:
 

Forum statistics

Threads
1,175,897
Messages
5,900,131
Members
434,818
Latest member
ankurc_dk

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top