Select a column based upon a value chosen
- Thread starter rjwalters
- Start date
SirJB7
Excel Rōnin
Hi, rjwalters!
Let me see if I got it:
- in row 6 you have columns with values P, S or other (BTW, I hope you have 256 or 16384 columns, and in all rows not only in this
...)
- in other row you have a cell (e.g. A1) and when you select it you want to hide all columns that don't contain a P in row 6
- and what about how to show them again?
- and anything similar for S values, or for other values?
Regards!
Let me see if I got it:
- in row 6 you have columns with values P, S or other (BTW, I hope you have 256 or 16384 columns, and in all rows not only in this
...)- in other row you have a cell (e.g. A1) and when you select it you want to hide all columns that don't contain a P in row 6
- and what about how to show them again?
- and anything similar for S values, or for other values?
Regards!
I have Excel 2010. In row 6 I have either an S or P in column F thru EH
You are correct in what I need my action to be. If I select "S" I want only the columns that have a "S" in row six to be visible, much like a filter would work. And the same if a "P" is selected, would only show the columns with a "P" in row six.
Right now I have a "P" in G1 and a "S" in H1.
You are correct in what I need my action to be. If I select "S" I want only the columns that have a "S" in row six to be visible, much like a filter would work. And the same if a "P" is selected, would only show the columns with a "P" in row six.
Right now I have a "P" in G1 and a "S" in H1.
SirJB7
Excel Rōnin
Hi, rjwalters!
Give a look at this file
https://dl.dropboxusercontent.com/u/60558749/Select%20a%20column%20based%20upon%20a%20value%20chosen%20%28for%20rjwalters%20at%20chandoo.org%29.xlsm
It uses the following named ranges:
- dynamic
DataTable, your whole data
DataPSTable, your data in columns F:EH
KeyCells, row 6 of DataPSTable
- normal
ParamAllCell, for clicking and showing all columns
ParamPCell, idem for P in row 6
ParamSCell, idem for S in row 6
I placed these last 3 outside the F:EH range (you placed the last 2 in columns G:H) because of hiding/showing easiness.
This is the code in the worksheet object section:
-----
[pre]
[/pre]
-----
Just advise if any issue.
Regards!
Give a look at this file
https://dl.dropboxusercontent.com/u/60558749/Select%20a%20column%20based%20upon%20a%20value%20chosen%20%28for%20rjwalters%20at%20chandoo.org%29.xlsm
It uses the following named ranges:
- dynamic
DataTable, your whole data
DataPSTable, your data in columns F:EH
KeyCells, row 6 of DataPSTable
- normal
ParamAllCell, for clicking and showing all columns
ParamPCell, idem for P in row 6
ParamSCell, idem for S in row 6
I placed these last 3 outside the F:EH range (you placed the last 2 in columns G:H) because of hiding/showing easiness.
This is the code in the worksheet object section:
-----
[pre]
Code:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
' constants
' ranges
Const ksKey = "KeyCells"
Const ksParamAll = "ParamAllCell"
Const ksParamP = "ParamPCell"
Const ksParamS = "ParamSCell"
' values
Const ksTypeAll = "*"
Const ksTypeP = "P"
Const ksTypeS = "S"
'
' declarations
Dim rngK As Range, rngParamAll As Range, rngParamP As Range, rngParamS As Range
Dim sKey As String
Dim I As Integer
'
' start
' application
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
' parameters
Set rngParamAll = Range(ksParamAll)
Set rngParamP = Range(ksParamP)
Set rngParamS = Range(ksParamS)
' check
If Target.Cells.Count = 1 Then
If Application.Intersect(Target, rngParamAll) Is Nothing Then
If Application.Intersect(Target, rngParamP) Is Nothing Then
If Application.Intersect(Target, rngParamS) Is Nothing Then
sKey = ""
Else
sKey = ksTypeS
End If
Else
sKey = ksTypeP
End If
Else
sKey = ksTypeAll
End If
Else
sKey = ""
End If
' go?
If sKey = "" Then GoTo Worksheet_SelectionChange_Exit
' ranges
Set rngK = Range(ksKey)
'
' process
With rngK
' show all
.EntireColumn.Hidden = False
For I = 1 To .Columns.Count
If Not (.Cells(1, I).Value Like sKey) Then
' hide not matching
ActiveSheet.Columns(I + .Column - 1).Hidden = True
End If
Next I
End With
'
' end
' ranges
Set rngK = Nothing
Beep
Worksheet_SelectionChange_Exit:
Set rngParamS = Nothing
Set rngParamP = Nothing
Set rngParamAll = Nothing
' application
With Application
.EnableEvents = True
.ScreenUpdating = True
End With
'
End Sub-----
Just advise if any issue.
Regards!
I can not figure out what I am doing wrong. I copied the code into my VB editor for the worksheet. I am using Sheet 2 in my book. I have my C1 D1 E1 as the cells for the commands, but I am getting nothing. The book you attached is doing exactly what I was wanting. I also have the Macro added in that was in your workbook.
SirJB7
Excel Rōnin
Hi, rjwalters!
If you look into the macro code, you'll see several ranges definition:
-----
Set rngParamAll = Range(ksParamAll)
Set rngParamP = Range(ksParamP)
Set rngParamS = Range(ksParamS)
Set rngK = Range(ksKey)
-----
which reference to the constants at the beginning of the code, which holds the actual names of the ranges in the workbook.
Check their definitions into my file, and then set them properly in your actual workbook.
Regards!
If you look into the macro code, you'll see several ranges definition:
-----
Set rngParamAll = Range(ksParamAll)
Set rngParamP = Range(ksParamP)
Set rngParamS = Range(ksParamS)
Set rngK = Range(ksKey)
-----
which reference to the constants at the beginning of the code, which holds the actual names of the ranges in the workbook.
Check their definitions into my file, and then set them properly in your actual workbook.
Regards!
SirJB7
Excel Rōnin
Hi, rjwalters!
No, Macro 4 it's a residual work module left by the automatic built-in macro recorder.
From Excel, not from the VBA editor, go to Formules tab, Names Manager, and check the definitions from there. Within the code in VBA you'll only need to change the value of the constants at the top of the procedure, and only if you happen to change them from those that I set. Note that the worksheet name is within a constant too, just for not having to touch so deeply the code, only the constants definition.
Regards!
No, Macro 4 it's a residual work module left by the automatic built-in macro recorder.
From Excel, not from the VBA editor, go to Formules tab, Names Manager, and check the definitions from there. Within the code in VBA you'll only need to change the value of the constants at the top of the procedure, and only if you happen to change them from those that I set. Note that the worksheet name is within a constant too, just for not having to touch so deeply the code, only the constants definition.
Regards!
SirJB7
Excel Rōnin
Hi, rjwalters!
If you want to post your email here, I'll get back to you now and edit your address from the post. Otherwise I don't know how to explain you how to set your ranges. But the structure of the workbook that I uploaded matches with your requirements, so if you didn't missed or changed anything copying exactly the definitions would be enough.
Regards!
If you want to post your email here, I'll get back to you now and edit your address from the post. Otherwise I don't know how to explain you how to set your ranges. But the structure of the workbook that I uploaded matches with your requirements, so if you didn't missed or changed anything copying exactly the definitions would be enough.
Regards!