View Full Version : Solved: Filter and VBA
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
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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.