Forum Discussion
Need help Hiding Columns based on dropdown list value
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 Sub
HansVogelaar 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 SubThank you so much. I must be doing something wrong. The first code worked perfectly but this one is not working at all for me. I made the necessary changes but still nothing.
**Please do not feel you have to keep helping me, you have done a lot already and I really appreciate it. I will understand if you ignore me! lolI added a photo because I am not sure if it matters - Row 1 is a header row. My entries will start on Row 2 (I have examples listed) I have a drop-down selection to mark the status in column A as Pending or Complete.
When I mark Column A as Complete on any row it doesn't move.
I attached a picture of what my project looks like to see if that helps. I am trying to make everything as automated as possible. There is a 3rd sheet that is hidden that I used for my dropdown lists.
Did you copy the code into the worksheet module, as described in my first reply?