I can not figure out how to auto-filter columns to display just the
way auto-filter dynamically filters what rows to display. The only
option I can think of is GROUPING, but that is a far cry from being an
optimal solution. Any suggestions would be appreciated.

I think you would need a macro to do this - there is no built-in
feature to allow you to do it.

Pete

On Oct 24, 1:24 am, wrote:
I can not figure out how to auto-filter columns to display just the
way auto-filter dynamically filters what rows to display. The only
option I can think of is GROUPING, but that is a far cry from being an
optimal solution. Any suggestions would be appreciated.

You could look at View|Custom views (xl2003 menu system)
or provide a macro that hides/shows columns the way you like.

wrote:

I can not figure out how to auto-filter columns to display just the
way auto-filter dynamically filters what rows to display. The only
option I can think of is GROUPING, but that is a far cry from being an
optimal solution. Any suggestions would be appreciated.

--

Dave Peterson

On Oct 23, 5:36 pm, Pete_UK wrote:
I think you would need a macro to do this - there is no built-in
feature to allow you to do it.

Pete

On Oct 24, 1:24 am, wrote:



I can not figure out how to auto-filter columns to display just the
way auto-filter dynamically filters what rows to display. The only
option I can think of is GROUPING, but that is a far cry from being an
optimal solution. Any suggestions would be appreciated.- Hide quoted text -

- Show quoted text -

Pete,
This is a good suggestion and something I had tried before. The
problem is just like how normal auto-filtering works, I have column
headers being added/modified/deleted with enough of a freqnecy that
always putting them in sequential columns would not be practical. If
I come up null on this, this would probably be my PLAN B. Thanks.

On Oct 23, 5:44 pm, Dave Peterson wrote:
You could look at View|Custom views (xl2003 menu system)
or provide a macro that hides/shows columns the way you like.

wrote:

I can not figure out how to auto-filter columns to display just the
way auto-filter dynamically filters what rows to display. The only
option I can think of is GROUPING, but that is a far cry from being an
optimal solution. Any suggestions would be appreciated.

--

Dave Peterson

This will be my PLAN B. Thanks.

Hi

Take a look at Debra Dalgleish's site.
Debra has created a sample workbook which will hide certain columns,
dependent upon criteria that you enter in a cell. This will give you a good
start to achieve what you want.
The file can be downloaded at
http://www.contextures.com/HideMarkedCols.zip

--
Regards
Roger Govier



wrote in message
oups.com...
I can not figure out how to auto-filter columns to display just the
way auto-filter dynamically filters what rows to display. The only
option I can think of is GROUPING, but that is a far cry from being an
optimal solution. Any suggestions would be appreciated.

On Oct 24, 2:19 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi

Take a look at Debra Dalgleish's site.
Debra has created a sample workbook which will hide certain columns,
dependent upon criteria that you enter in a cell. This will give you a good
start to achieve what you want.
The file can be downloaded athttp://www.contextures.com/HideMarkedCols.zip

--
Regards
Roger Govier

wrote in message

oups.com...



I can not figure out how to auto-filter columns to display just the
way auto-filter dynamically filters what rows to display. The only
option I can think of is GROUPING, but that is a far cry from being an
optimal solution. Any suggestions would be appreciated.- Hide quoted text -

- Show quoted text -

I'll give this a look. Thanks.

I adapted this macro from J.Walk to hide selected rows. It displays a
dialog box listing all available columns, and hides the ones that are
checked.


Public Sub SelectColumnsToHide()
Dim i As Integer, iColumnNumber As Integer
Dim TopPos As Integer, LeftPos As Integer
Dim ColumnCount As Integer
Dim PrintDlg As DialogSheet
Dim cb As CheckBox

Dim rngSheet As Excel.Range
Dim rngHeader As Excel.Range
Dim wshSheet As Excel.Worksheet
Dim strHeader As String
Dim maxTopPos As Integer
Dim dialogColumns As Integer

Const topPosShift As Integer = 13
Const leftPosShift As Integer = 150
Const initialTopPos As Integer = 40
Const initialLeftPos As Integer = 78
Const rowsPerDialogColumn As Integer = 30

On Error Resume Next
Set wshSheet = Application.ActiveSheet

If wshSheet Is Nothing Then
Call MsgBox("You must perform this action on a sheet that
actually has columns.", _
vbOKOnly + vbInformation, "Well DUH!")
Exit Sub
End If

Set rngHeader = Application.InputBox("Select a cell in the header
row", "Looking for the headers", , , , , , 8)
On Error GoTo 0

If rngHeader Is Nothing Then Exit Sub

Set rngHeader = rngHeader.EntireRow.Cells(1, 1)

Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical + vbOKOnly, "Can't
do this"
Exit Sub
End If

' Add a temporary dialog sheet
Set wshSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

ColumnCount = 0

' Add the checkboxes
dialogColumns = 1
maxTopPos = 0
TopPos = initialTopPos
LeftPos = initialLeftPos
For i = 1 To wshSheet.UsedRange.Columns.Count
strHeader = rngHeader.Offset(0, i - 1)

If Len(strHeader) 0 Then
ColumnCount = ColumnCount + 1
PrintDlg.CheckBoxes.Add LeftPos, TopPos, 150, 16.5
PrintDlg.CheckBoxes(ColumnCount).Text = i & " - " &
strHeader
TopPos = TopPos + topPosShift
If (TopPos = initialTopPos + rowsPerDialogColumn *
topPosShift) Then
dialogColumns = dialogColumns + 1
maxTopPos = TopPos
TopPos = initialTopPos
LeftPos = LeftPos + leftPosShift
End If
End If
Next i

If (maxTopPos = 0) Then
maxTopPos = TopPos
End If

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 140 + dialogColumns * leftPosShift

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max(68, PrintDlg.DialogFrame.Top +
maxTopPos - 34)
.Width = 130 + dialogColumns * leftPosShift
.Caption = "Select columns to hide"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
With PrintDlg
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

' Display the dialog box
If ColumnCount < 0 Then
If .Show Then
For i = 1 To .CheckBoxes.Count
If .CheckBoxes(i).Value = xlOn Then
' extract column number
iColumnNumber = Left(.CheckBoxes(i).Caption,
InStr(1, .CheckBoxes(i).Caption, " "))
wshSheet.Cells(1,
iColumnNumber).EntireColumn.ColumnWidth = 0
End If
Next i
End If
Else
Call MsgBox("Sheet must have a row with headers for this
to work.", vbOKOnly + vbInformation)
End If
End With

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
Application.DisplayAlerts = True

' Reactivate original sheet
Application.ScreenUpdating = True
End Sub



On Oct 24, 9:02 am, wrote:
On Oct 24, 2:19 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:





Hi

Take a look at Debra Dalgleish's site.
Debra has created a sample workbook which will hide certain columns,
dependent upon criteria that you enter in a cell. This will give you a good
start to achieve what you want.
The file can be downloaded athttp://www.contextures.com/HideMarkedCols.zip

--
Regards
Roger Govier

wrote in message

roups.com...

I can not figure out how to auto-filter columns to display just the
way auto-filter dynamically filters what rows to display. The only
option I can think of is GROUPING, but that is a far cry from being an
optimal solution. Any suggestions would be appreciated.- Hide quoted text -

- Show quoted text -

I'll give this a look. Thanks.- Hide quoted text -

- Show quoted text -

Hi

That's very nice code that you have adapted.
I think I would probably amend it slightly, so that you could also unhide
columns that had previously been hidden, by changing the line after

iColumnNumber = Left(.CheckBoxes(i).Caption, _
InStr(1, .CheckBoxes(i).Caption, " "))

to

If wshSheet.Cells(1, iColumnNumber).EntireColumn.Hidden = False Then
wshSheet.Cells(1, iColumnNumber).EntireColumn.Hidden = True
Else
wshSheet.Cells(1, iColumnNumber).EntireColumn.Hidden = False
End If

--
Regards
Roger Govier



"iliace" wrote in message
oups.com...
I adapted this macro from J.Walk to hide selected rows. It displays a
dialog box listing all available columns, and hides the ones that are
checked.


Public Sub SelectColumnsToHide()
Dim i As Integer, iColumnNumber As Integer
Dim TopPos As Integer, LeftPos As Integer
Dim ColumnCount As Integer
Dim PrintDlg As DialogSheet
Dim cb As CheckBox

Dim rngSheet As Excel.Range
Dim rngHeader As Excel.Range
Dim wshSheet As Excel.Worksheet
Dim strHeader As String
Dim maxTopPos As Integer
Dim dialogColumns As Integer

Const topPosShift As Integer = 13
Const leftPosShift As Integer = 150
Const initialTopPos As Integer = 40
Const initialLeftPos As Integer = 78
Const rowsPerDialogColumn As Integer = 30

On Error Resume Next
Set wshSheet = Application.ActiveSheet

If wshSheet Is Nothing Then
Call MsgBox("You must perform this action on a sheet that
actually has columns.", _
vbOKOnly + vbInformation, "Well DUH!")
Exit Sub
End If

Set rngHeader = Application.InputBox("Select a cell in the header
row", "Looking for the headers", , , , , , 8)
On Error GoTo 0

If rngHeader Is Nothing Then Exit Sub

Set rngHeader = rngHeader.EntireRow.Cells(1, 1)

Application.ScreenUpdating = False

' Check for protected workbook
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical + vbOKOnly, "Can't
do this"
Exit Sub
End If

' Add a temporary dialog sheet
Set wshSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook.DialogSheets.Add

ColumnCount = 0

' Add the checkboxes
dialogColumns = 1
maxTopPos = 0
TopPos = initialTopPos
LeftPos = initialLeftPos
For i = 1 To wshSheet.UsedRange.Columns.Count
strHeader = rngHeader.Offset(0, i - 1)

If Len(strHeader) 0 Then
ColumnCount = ColumnCount + 1
PrintDlg.CheckBoxes.Add LeftPos, TopPos, 150, 16.5
PrintDlg.CheckBoxes(ColumnCount).Text = i & " - " &
strHeader
TopPos = TopPos + topPosShift
If (TopPos = initialTopPos + rowsPerDialogColumn *
topPosShift) Then
dialogColumns = dialogColumns + 1
maxTopPos = TopPos
TopPos = initialTopPos
LeftPos = LeftPos + leftPosShift
End If
End If
Next i

If (maxTopPos = 0) Then
maxTopPos = TopPos
End If

' Move the OK and Cancel buttons
PrintDlg.Buttons.Left = 140 + dialogColumns * leftPosShift

' Set dialog height, width, and caption
With PrintDlg.DialogFrame
.Height = Application.Max(68, PrintDlg.DialogFrame.Top +
maxTopPos - 34)
.Width = 130 + dialogColumns * leftPosShift
.Caption = "Select columns to hide"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
With PrintDlg
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

' Display the dialog box
If ColumnCount < 0 Then
If .Show Then
For i = 1 To .CheckBoxes.Count
If .CheckBoxes(i).Value = xlOn Then
' extract column number
iColumnNumber = Left(.CheckBoxes(i).Caption,
InStr(1, .CheckBoxes(i).Caption, " "))
wshSheet.Cells(1,
iColumnNumber).EntireColumn.ColumnWidth = 0
End If
Next i
End If
Else
Call MsgBox("Sheet must have a row with headers for this
to work.", vbOKOnly + vbInformation)
End If
End With

' Delete temporary dialog sheet (without a warning)
Application.DisplayAlerts = False
PrintDlg.Delete
Application.DisplayAlerts = True

' Reactivate original sheet
Application.ScreenUpdating = True
End Sub



On Oct 24, 9:02 am, wrote:
On Oct 24, 2:19 am, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:





Hi

Take a look at Debra Dalgleish's site.
Debra has created a sample workbook which will hide certain columns,
dependent upon criteria that you enter in a cell. This will give you a
good
start to achieve what you want.
The file can be downloaded
athttp://www.contextures.com/HideMarkedCols.zip

--
Regards
Roger Govier

wrote in message

roups.com...

I can not figure out how to auto-filter columns to display just the
way auto-filter dynamically filters what rows to display. The only
option I can think of is GROUPING, but that is a far cry from being
an
optimal solution. Any suggestions would be appreciated.- Hide quoted
text -

- Show quoted text -

I'll give this a look. Thanks.- Hide quoted text -

- Show quoted text -