Excel 2003 Auto filter macro

[MJamison07]

Let me just say that I am not even in macro kindergarten. But with some help from some posts I read & with some macros I recorded, I managed to put together this macro. To my amazement, it worked! I make a selection from a drop down list in cell B1. While I still have B1 selected, I run the macro. It finds the selection in B1 in a column heading and then filters that column for non-blanks.

What I have not been able to do is make the macro run as soon as I make my selection in the drop down list in B1. Can you help me with that?

Thanks – MJ


Sub Macro5()
'
' Macro5 Macro

Cells.Find(What:=ActiveCell, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Cells.AutoFilter
Cells.AutoFilter Field:=ActiveCell.Column, Criteria1:="<>"

End Sub

 

[SkipVought]

hi,

What do you want the macro that you wrote, to do?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MJamison07]

I want the macro to start upon the selection from the drop down that is in cell b1 (made through Validation list).

 

[SkipVought]

That is not the question I asked.

What do you want Macro5, "the macro that you wrote", to do? It is not clear, what your intent was whn you created, and I need to know.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MJamison07]

Sorry - let me try again. I have a file with a list of tasks with the team members. Simplified version:

Dropdown Cell

Person 1 Person 2 Person 3
Task 1 R S I
Task 2 S R
Task 3 R S

In the drop down cell I have a list of Person 1, Person 2, Person 3. Based on the selection, I want that Person's column to filter for the nonblank cells. So if I select Person 3, you should only see this:

Person 3

Tasks Person 1 Person 2 Person 3
Task 1 R S I
Task 3 R S

Thanks.

 

[Gavona]

You probably want to take off any existing filter (hint: record yourself doing data,filter,showall)

To automatically run the macro you will need to use the worksheet_change event (have a look in help).
BUT FIRST: make sure your macro is doing everything you want.

You don't need Cells.autofilter
The correct range will be better filtered with
ActiveCell.AutoFilter Field:=ActiveCell.Column, Criteria1:="<>"

Posts about macros/VBA belong in forum707

Gavin