Excel VBA-select named column based on variable of a cell's value

jrico

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello everyone. Happy Holidays! I promise that I've spent hours searching for a solution to my Excel vba/macro issue. I've also attempted using information found in this forum and other excel vba forums to string together a viable script, but to no avail.

Objective: to select a named column from a range of columns based on a cell's value and to hide remaining columns from the range of columns.
Facts:
1. A worksheet consists of 51 columns.
2. Column A consists of a listing of factoids/characteristics.
3. Columns B through AY is a named range = "State"
4. 'Name Box' was used to assign a state to a column. For example, Column B is named Alabama, Column C is named Alaska, and so on).
2. Cell reference A1 is where a user is to enter the name of a state, such as Alaska, Maine, Texas, etc., to view applicable factoids/characteristics
3. When running the macro, column A will always remain unhidden, the column corresponding to the state referenced in cell A1 will remain unhidden but the remaining columns defined by range "State" will be hidden

I hope I have entered enough information to solicit a response. Your help is appreciated. Thank you.
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub jrico()
   Dim Fnd As Range
   
   Range("State").EntireColumn.Hidden = True
   Set Fnd = Range("State").Find(Range("A1"), , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.EntireColumn.Hidden = False
End Sub
 
Solution

jrico

New Member
Joined
Dec 21, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
Sub jrico()
   Dim Fnd As Range
  
   Range("State").EntireColumn.Hidden = True
   Set Fnd = Range("State").Find(Range("A1"), , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.EntireColumn.Hidden = False
End Sub
Fluff,
You are my hero today. If you don't mind, would you please briefly explain the commas in row beginning with Set Fnd=Range...... Thank you.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,269
Office Version
  1. 365
Platform
  1. Windows
The commas are a short form way of writing the Find function without the need to specify the name of each argument.
You can see what the arguments are here Range.Find method (Excel)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,253
Messages
5,600,544
Members
414,387
Latest member
Vincent88

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