Filters

[dazoloko via OfficeKB.com]

I have a spreadsheet containing Training Information. A simplified version is
laid out below :-

The rows contain the Staff Name and Service Area and the and the Columns
contain the courses available. If the course is required by the staff member
a priority Letter is plotted in to the matrix
eg Darren requires Course 1 on a Priority A.

Now heres the tricky bit !! What I want to be able to do is filter on both
rows and columns so if i filter on Finance it will return Darren and Johns
Info but only show courses 1 and 2 and not course 3 as neither of those
require that.

Likewise if I filter on Darren it will only show Course 1

Name Service Area Course 1 Course 2 Course 3
Darren Finance A
John Finance A A
Alan Admin A A
David Admin A

I have the following Macro Attached to a Command button which works fine
until I filter the information.

Sub HideCols()


Dim myCol As Range
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
For Each myCol In .UsedRange.Columns
If Application.CountA(.Range(.Cells(2, myCol.Column), _
.Cells(.Rows.Count, myCol.Column))) = 0 Then
'hide it
myCol.Hidden = True
Else
'unhide any previously hidden column???
myCol.Hidden = False
End If
Next myCol
End With

End Sub

Therefore the question is :-

How can I get a Macro to run on filtered data ?

Cheers

D

 

[Daniel.C]

Give a try to :

Sub HideCols()


Dim myCol As Range, myRange As Range
Dim wks As Worksheet

Set wks = Worksheets("Sheet1")

With wks
For Each myCol In [_filterdatabase].Columns
Set myRange = Intersect([_filterdatabase], myCol). _
SpecialCells(xlCellTypeVisible)
If Application.CountA(myRange) = 1 Then
'hide it
myCol.Hidden = True
Else
'unhide any previously hidden column???
myCol.Hidden = False
End If
Next myCol
End With

End Sub

Regards.
Daniel