How to move rows down to the bottom of my current worksheet once completed

[robboe]

I am trying to figure out how to set up my worksheet so that all the rows where column G says "completed" move down to the bottom of the current spreadsheet and also have the entire row automatically highlight grey once it's listed as completed as well. I think i'm going to leave Column G as a free-form one and just have people type in completed instead of using a drop down. I have found a couple codes on here, but none of them seemed to be exactly what i was looking for. I'm also not too familiar with VBA so would need some help with the steps to actually add the code. Any help would be greatly appreciated.

 

[DanteAmor]

Hi and welcome to MrExcel.

Put the following code in the events of your sheet.

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Go back to the sheet, change the status in column G.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range, mvrng As Range
  Dim lr As Long

  Set rng = Intersect(Target, Range("G2:G" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If LCase(c.Value) = LCase("completed") Then
        If mvrng Is Nothing Then Set mvrng = Range("A" & c.Row & ":I" & c.Row) Else Set mvrng = Union(mvrng, Range("A" & c.Row & ":I" & c.Row))
      End If
    Next
    If Not mvrng Is Nothing Then
      Application.EnableEvents = False
      mvrng.Interior.ColorIndex = 15
      mvrng.Copy Range("A" & Rows.Count).End(3)(2)
      mvrng.Delete Shift:=xlUp
      Application.EnableEvents = True
    End If
  End If
End Sub

 

[robboe]

Hi and welcome to MrExcel.

Put the following code in the events of your sheet.

SHEET EVENT
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

Go back to the sheet, change the status in column G.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim rng As Range, c As Range, mvrng As Range
  Dim lr As Long

  Set rng = Intersect(Target, Range("G2:G" & Rows.Count))
  If Not rng Is Nothing Then
    For Each c In rng
      If LCase(c.Value) = LCase("completed") Then
        If mvrng Is Nothing Then Set mvrng = Range("A" & c.Row & ":I" & c.Row) Else Set mvrng = Union(mvrng, Range("A" & c.Row & ":I" & c.Row))
      End If
    Next
    If Not mvrng Is Nothing Then
      Application.EnableEvents = False
      mvrng.Interior.ColorIndex = 15
      mvrng.Copy Range("A" & Rows.Count).End(3)(2)
      mvrng.Delete Shift:=xlUp
      Application.EnableEvents = True
    End If
  End If
End Sub

That worked in the practice spreadsheet i was testing it on, but when I tried to get it to work in my spreadsheet at work, it's either moving the "completed" items to the top or they are disappearing and not just going to the bottom of the spreadsheet like they did in my test spreadsheet. Here is what the actual blank spreadsheet looks like- does something in the code need to be changed?

 

[robboe]

That worked in the practice spreadsheet i was testing it on, but when I tried to get it to work in my spreadsheet at work, it's either moving the "completed" items to the top or they are disappearing and not just going to the bottom of the spreadsheet like they did in my test spreadsheet. Here is what the actual blank spreadsheet looks like- does something in the code need to be changed?
View attachment 55589

Nevermind - I think it was because i didnt have any data in the spreadsheet yet. Once I added items to the list it worked. thank you!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.