Move a row to the top of a sheet upon drop down selection.

[afspeck37]

Hello everyone,

I currently have a working priority list for taskers that my company uses. I have conditional formatting so that once I select "Complete" from of the columns it will highlight that row. What I am trying to do is once I select "Complete" is to not only change the color but also move that entire row to the top of the sheet. I was able to search and find a way to move the row to a different sheet, but I would like to keep that row on the same sheet. Any help is appreciated. Thank you.

 

[Micron]

Maybe this (worksheet SelectionChange event?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Count = Columns.Count And Target.row <> 1 And Target.Rows.Count < 2 Then
    Target.EntireRow.Cut
    Range("A1").Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
End If

End Sub

Only try this in a copy of your workbook file. To use that code, click on the row number at the far left for the row you want to move. Not sure it's a great idea, as you'll never be able to select an entire row for any other purpose without having it moved. That should probably present a message box with yes/no options, as long as you're not doing this too often.

 

[Peter_SSs]

once I select "Complete" from of the columns

That does not make sense to me but you could try this (also with a copy of your workbook)

move that entire row to the top of the sheet.

I assume there are headings so "top of the sheet" means row 2?

Post back if you need help with how to implement this.

BTW, I suggest that you update your forum profile (click your user name at the top right of the forum, then ‘Account details’) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.CountLarge = 1 Then
    If Target.Value = "Complete" Then
      Application.EnableEvents = False
      Target.EntireRow.Cut
      Rows(2).Insert
      Application.EnableEvents = True
    End If
  End If
End Sub