Automatically move row to top when cell text changed

Question

Is there a way to move a row to the top of the spreadsheet every time one of the cells in the row that was labeled open is changed to closed?

I want this action every time not just once. I get them all at the top with sort. They do not automatically go. I have to sort each time.

Sub DiscrepancyReportMacroStepTwo()
Dim cel As Range, col As Range, curRow As Long, temp 
For Each col In Sheets("Master list").UsedRange.Columns
   curRow = 1
   For Each cel In col.Cells
      If cel.Value2 Like "Closed" Then ' Swap values of the cell and a cel from top of the column (at curRow)
         temp = col.Cells(curRow).Value2
         col.Cells(curRow).Value2 = cel.Value2
         cel.Value2 = temp
         curRow = curRow + 1
       End If
    Next cel
Next col
End Sub

I have this but it doesn't work automatically.
enter image description here

Is it possible to see sample data and expected outcome? It would also be nice to know what you have tried so far. — cybernetic.nomad
– cybernetic.nomad, Commented Jun 19 at 15:35
In VBA create a Worksheet_Change function that will be called every time a cell is changed on the worksheet. In your code, first test that the open / close status column was what changed and if so invoke the range.sort() method to resort your table. — Chris Maurer
– Chris Maurer, Commented Jun 19 at 16:03
how do i do that.. all of my excel skills have been from googling how to do things lo — Elizabeth Rohs
– Elizabeth Rohs, Commented Jun 19 at 16:12
This will be much easier if your data is in a Table/ListObject and not just a regular range. — Tim Williams
– Tim Williams, Commented Jun 19 at 17:00
In which column are "Open" or "Closed" found? Are they always spelled like this? How does the change happen, manually or by formula? If the latter, what's the formula? — VBasic2008
– VBasic2008, Commented Jun 19 at 22:39

VBasic2008 · Accepted Answer · 2025-06-24 23:21:37Z

A Worksheet Change: Move Row of Changed Cell

Sheet Module, e.g. Sheet1

The location of your code is wrong. Your screenshot shows that it is located in the standard module Module3 when it should be in the correct sheet module. Best right-click the sheet's tab and select View Code. Copy the following code into the window that opens.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' Define constants.
    Const HEADER_ROW As Long = 1
    Const STATUS_COLUMN As Long = 3
    Const MOVE_STATUS As String = "Closed"

    ' Restrict to a single cell changed.
    If Target.Cells.CountLarge > 1 Then Exit Sub
    
    ' Reference the target range.
    Dim trg As Range:
    With Me.UsedRange
        If .Rows.Count <= HEADER_ROW Then Exit Sub
        Set trg = Intersect(.Resize(.Rows.Count - HEADER_ROW) _
            .Offset(HEADER_ROW), Me.Columns(STATUS_COLUMN))
        If trg Is Nothing Then Exit Sub
    End With
    
    ' Check if the cell is not part of the target range.
    Set trg = Intersect(trg, Target)
    If trg Is Nothing Then Exit Sub
        
    ' Disable events before writing to the same worksheet.
    Application.EnableEvents = False
    
    ' Move row if matching status.
    If StrComp(CStr(Target.Value), MOVE_STATUS, vbTextCompare) = 0 Then
        Me.Rows(Target.Row).Cut
        Me.Rows(1).Offset(HEADER_ROW).Insert
    End If
    
    ' Don't forget to reenable events.
    Application.EnableEvents = True

End Sub

usncahill · Accepted Answer · 2025-06-19 18:36:09Z

0

There are a few solutions, some automated, some manual.
1. Apply a filter to the range, filter the column containing the open-close statuses, update cells, then hit key combo (Ctrl+Alt+L) to reapply the filter after update. (Sounds like you want automation, but I figured I'd throw in a simple manual method also).

2. VBA. The Worksheet_Change event of the worksheet can be used to sense changes to just the desired column and implement a specific response. My response below is specifically what you initially asked for, moving the row to the top, but you could apply a sort in a similar manner.
Update 1: added strMoveStatus to allow you to set the status that causes the row to move more easliy. Added Application.EnableEvents = False and = True to turn off and on events to prevent self triggering per user suggestions.
Update 2: just grab the parts of the code below to your existing Worksheet_Change. If you want the Const more local to the Sub, be aware of the when they are used; nomColStatus would need to be declared outside the If.

'Stuff to add to the top of your worksheet's VBA module
Const numRowHeader = 1
Const numColStatus = 3
Const strMoveStatus = "Closed"

Private Sub Worksheet_Change(ByVal Target As Range)

    ' Your original worksheet_change content here

' Stuff to add to your current Worksheet_Change \/ \/ \/ \/ \/
    ' List of reasons this should run
    If Target.Column = numColStatus and _
        Target.Columns.Count = 1 and _
        Target.Rows.Count = 1 Then 
        
        Application.EnableEvents = False 'prevent self triggering
    
        If Target.Value = strMoveStatus Then
            Me.Rows(Target.Row).Cut
            Me.Rows(1).Offset(numRowHeader).Insert
        End If
    
        Application.EnableEvents = True 'allow future triggering
    End If
' Stuff to add to your current Worksheet_Change ^  ^  ^  ^  ^

End Sub

Before update:

After update:

edited Jun 19 at 18:36

answered Jun 19 at 17:01

usncahill

4646 silver badges16 bronze badges

15 Comments

Elizabeth Rohs Jun 19 at 17:35

ok awesome! thank you so much! how do i run it after i put that in there? when I try to run it nothing comes up?

CDP1802 Jun 19 at 17:37

Changing a worksheet inside a Worksheet_Change Sub without first disabling events with Application.EnableEvents = False ?? Try code with Const numColStatus = 1

usncahill Jun 19 at 17:46

@ElizabethRohs this will run automatically when the column number matching numColStatus (i.e. A=1, B=2, C=3,...) is updated if the workbook has Macros turned on. Please note CDP's concern: I will adjust the code to put some infinite recursion protection (Application.EnabledEvents = False to prevent the code from triggering itself.

Elizabeth Rohs Jun 19 at 17:54

@usncahill I also was also putting it in as a module not on the worksheet page so i fixed that! but let me try redoing what you just said you fixed! I am so sorry I am having to do this for my job and I am the least knowledgeable on excel so a girl is struggling

usncahill Jun 19 at 18:05

no worries; please use my latest updated code, incorporating CDP's suggestion. i am self-taught too. i still google something every day :D

|

Collectives™ on Stack Overflow

Automatically move row to top when cell text changed

2 Answers 2

A Worksheet Change: Move Row of Changed Cell

Comments

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

A Worksheet Change: Move Row of Changed Cell

Comments

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related