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

afspeck37

New Member
Joined
Apr 10, 2015
Messages
13
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.
 
Maybe this (worksheet SelectionChange event?
VBA Code:
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.
 
Upvote 0
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’)

VBA Code:
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top