Hide Columns based on a Cell Value

ian9999

New Member
Joined
Apr 5, 2008
Messages
46
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi all

I would really appreciate any help on this. I want to design a worksheet to hidecolumns depending on the value of cell A1. I am guessing I would need to use VBA to do this, but I only started looking at VBA very recently so don’t know how to do this.

The columns will start from C and contain 151 different suppliers ending at EW, we need to enter loads of data for each supplier and hiding the columns will greatly help with this data entry<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p></o:p>
<o:p></o:p>
Is it possible to have the VBA match the column heading against what is requested in A1 and hide all other columns?<o:p></o:p>
<o:p></o:p>
As far as I’m aware the other way would be write the VBA code for each column which would be a very long winded way for all 151 different suppliers

Just so you are aware I'm using Excel 2007
<o:p></o:p>
Looking forward for your response<o:p></o:p>
<o:p></o:p>
Ian :)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Maybe
Code:
Sub HideCols()
Dim rngTest, rngStart, rngUnion, rngC As Range
 
Set rngTest = Worksheets("Sheet3").Range("A1")
Set rngStart = Worksheets("Sheet3").Range("B1:H1")
Set rngUnion = Nothing
 
Application.ScreenUpdating = False
 
For Each rngC In rngStart
    If rngC = rngTest Then
        If rngUnion Is Nothing Then
            Set rngUnion = rngC
        Else
            Set rngUnion = Union(rngC, rngUnion)
        End If
    End If
Next rngC
 
rngStart.EntireColumn.Hidden = True
rngUnion.EntireColumn.Hidden = False
 
Application.ScreenUpdating = True
 
End Sub
 
Upvote 0

ian9999

New Member
Joined
Apr 5, 2008
Messages
46
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Yard

I have copied the code into the sheet both Sheet 1 and Sheet 3 without any success

I know I am doing somehting wrong, but I can't seemt o find out what

Can you please help me with this

Thanks

Ian
 
Upvote 0

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
Define "without any success".

Do you get an error when you run the code? If so, what?

Does nothing happen when you run the code?

Are you thinking "what do you mean, 'run the code'?!"?

...

Do you want the code to run every time you change the value in cell A1? If so, is A1 a manual entry, or is it the result of a calculation?
 
Upvote 0

ian9999

New Member
Joined
Apr 5, 2008
Messages
46
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi Yard

I am unable to upload a HTML copy of the spreadsheet here from work, I will upload the spreadsheet when I get home tonight

The update can either be as a result of a vlookup or if the user selects a certain supplier from a drop down list

I don't see any columns of data being hidden or no error report is being reported

Many thanks for your help regarding this matter

Ian
 
Upvote 0

Yard

Well-known Member
Joined
Nov 5, 2008
Messages
1,929
How can the update of A1 be either a VLOOKUP result or a manual entry? If you make a manual entry then you overwrite the VLOOKUP formula.

It's important to know, since it will determine the area of the code that we need to work on. If it's a manual entry then we can use the Worksheet_Change event. If it's the result of a formula we have to use the Worksheet_Calculate event (unless we can identify the manual entry which would lead to the formula changing).

Add this line of code, which will confirm that you have actually run the code or not:

Rich (BB code):
Sub HideCols()
Dim rngTest, rngStart, rngUnion, rngC As Range

MsgBox "Code is about to run"
....
 
Upvote 0

Forum statistics

Threads
1,191,173
Messages
5,985,097
Members
439,940
Latest member
Kyrad42

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