Forum Discussion
Need help Hiding Columns based on dropdown list value
It's been several years since I have last used Excel and I have forgotten a LOT!
I need help doing the following tasks and also knowing if it's even possible.
I am attempting to make a tracker for work to track the tasks that I have done and their status, etc. I am lost as to where to go for help but if you know where, or know of someone that can help with how to do the following I would appreciate the help more than you could ever know!
7 Replies
Let's say the drop down is in E1.
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Range("E1"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False Range("F1:P1").EntireColumn.Hidden = False Select Case Range("E1").Value Case "ER" Range("H1:P1").EntireColumn.Hidden = True Case "SA" Range("F1:G1,L1:P1").EntireColumn.Hidden = True Case "RQ" Range("F1:K1").EntireColumn.Hidden = True End Select Application.EnableEvents = True Application.ScreenUpdating = True End If End SubHansVogelaar Thank you so much! It was so kind of you to provide this. It worked perfectly, but I think my dreams were far too irrational. haha
I don't believe I really thought this through last Friday. I now realize it's impossible to get it to do what I want it to do.
I wanted this tracker to log each individual task that I do on Workday (tasks are ER, SA & RQ). For some reason, my big dream was to have it show certain columns on each row based on the drop-down selection in column E.
Now I see the columns that show will always depend on the selection of the first row.
I'm so sorry I wasted your time but appreciate it more than you could ever know!
Would you be able to help me with a code to move the row to the bottom/ or another worksheet if marked complete in column A?
To move a row to the bottom of the active sheet:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim trg As Range Dim r As Long If Not Intersect(Range("A:A"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each rng In Intersect(Range("A:A"), Target) If LCase(rng.Value) = "complete" Then Set trg = Range("A" & Rows.Count).End(xlUp).Offset(1) rng.EntireRow.Copy Destination:=trg rng.EntireRow.Delete End If Next rng Application.CutCopyMode = False Application.EnableEvents = True Application.ScreenUpdating = True End If End SubTo move a row to the bottom of another sheet, say Sheet2:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim trg As Range Dim r As Long If Not Intersect(Range("A:A"), Target) Is Nothing Then Application.ScreenUpdating = False Application.EnableEvents = False For Each rng In Intersect(Range("A:A"), Target) If LCase(rng.Value) = "complete" Then Set trg = Worksheets("Sheet2").Range("A" & Worksheets("Sheet2").Rows.Count).End(xlUp).Offset(1) rng.EntireRow.Copy Destination:=trg rng.EntireRow.Delete End If Next rng Application.CutCopyMode = False Application.EnableEvents = True Application.ScreenUpdating = True End If End Sub
