Forum Discussion
Need help Hiding Columns based on dropdown list value
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?
- I did, I followed the same steps, it worked with the first code but not the second. I even recreated the entire thing from start to finish and the code is not working. 😞