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.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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
 
Upvote 0
Solution
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.
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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
Back
Top