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
--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200811/1