PDA

View Full Version : Solved: Filter and VBA



Dais
07-19-2011, 10:09 AM
Hi everyone,
I am a newbie here. I am familiar with excel but have problems with marco…… all your helps and supports will be greatly appreciated!!
Would you please take a look at the spreadsheet I attached? As it demonstrates we have two groups – Group Scott needs to complete tasks A to G and Group Kevin should finish tasks H to K. The table also shows the steps and processes in details.
I want to insert a filer or button in cell A1 or somewhere else in the sheet. When I select or enter “Task A” in it, only the details for the process of Task A show up. Could you please give me your opinion or suggestion re how to do that?
Thanks very much for your help in advance!!!
Dais

CatDaddy
07-19-2011, 10:26 AM
Sub HideOtherTasks()
Dim curTask As String
Dim curCol As Long
Dim cell As Range
ActiveWorkbook.Sheets(1).Activate
Range("A1").Activate
curTask = "Task " & InputBox("Task: ", , "A")
For Each cell In Range("C2:M2")
If cell.Text = curTask Then
curCol = cell.Column
Else
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub

Dais
07-19-2011, 11:47 AM
Thanks so much, CatDaddy!! You're awesome!!!:bow:

Only two more questions:
1. what should I do if I need to change the task names? For example, I changed them to Jan, Feb, March, April, May, etc and wanted to get the results by entering the names.

After I updated that form, I tried to change the code to
curTask = "Task " & InputBox("Task: ", , "Jan")

But it doesn't work well. :banghead: :bug: :think:

2. The other question is: how to be back to the whole table after the individual task searching is done?

Thanks so much again!!!!


Sub HideOtherTasks()
Dim curTask As String
Dim curCol As Long
Dim cell As Range
ActiveWorkbook.Sheets(1).Activate
Range("A1").Activate
curTask = "Task " & InputBox("Task: ", , "A")
For Each cell In Range("C2:M2")
If cell.Text = curTask Then
curCol = cell.Column
Else
cell.EntireColumn.Hidden = True
End If
Next cell
End Sub

CatDaddy
07-19-2011, 12:21 PM
Thanks so much, CatDaddy!! You're awesome!!!:bow:

Only two more questions:
1. what should I do if I need to change the task names? For example, I changed them to Jan, Feb, March, April, May, etc and wanted to get the results by entering the names.

After I updated that form, I tried to change the code to
curTask = "Task " & InputBox("Task: ", , "Jan")

But it doesn't work well. :banghead: :bug: :think:

2. The other question is: how to be back to the whole table after the individual task searching is done?

Thanks so much again!!!!

1. The syntax around choosing the task is based on how it looked in your spreadsheet curTask contains "Task" " " and "A" as it stands because the column headers are named "Task A", if the monthly tasks are named differently you will need to change how curTask is set.

2.Add a new sub to your macro for unhide
Sub unHide ()

ActiveSheet.Rows.Hidden = False

End Sub

Dais
07-21-2011, 03:00 PM
1. The syntax around choosing the task is based on how it looked in your spreadsheet curTask contains "Task" " " and "A" as it stands because the column headers are named "Task A", if the monthly tasks are named differently you will need to change how curTask is set.

2.Add a new sub to your macro for unhide
Sub unHide ()

ActiveSheet.Rows.Hidden = False

End Sub

Thanks so much!! It works very well.:thumb :thumb :thumb