Macro to move a cell up when a cell was removed to another sheet.

ucanad1yn · Nov 20th 2012

I have attached my excel sheet. I will will have endless rows, but right now my example shows 9 rows with columns A -K so for example, if row 3 column K in the "2013" worksheet has complete then it will move it over to the "completed" sheet but now that column 3 will be blank and I want the next cell with all the values to move up and so forth. I hope this makes sense.

i currently have a macro set up that everytime I enter "complete" in column K then it will move that row to the completed sheet and so on. But I don't know how to have it so that when that row is removed then the next row automatically moves up.

LearningVBANow · Nov 20th 2012

Re: Macro to move a cell up when a cell was removed to another sheet.

Put this code in the 2013 worksheet

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set MyRange = Range(Range("K2"), Range("K65536").End(xlUp))
    If Not Intersect(Target, MyRange) Is Nothing Then
        If Target.Value = "Complete" Then
            Range(Target, Target.Offset(0, -10)).Copy Destination:=Sheets("Completed").Range("A65536").End(xlUp).Offset(1, 0)
            Target.EntireRow.Delete
        End If
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Display More

If you just want the values without the formatting:

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyRange As Range
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Set MyRange = Range(Range("K2"), Range("K65536").End(xlUp))
    If Not Intersect(Target, MyRange) Is Nothing Then
        If Target.Value = "Complete" Then
            Range(Target, Target.Offset(0, -10)).Copy
            Sheets("Completed").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
            Target.EntireRow.Delete
        End If
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

Display More

Macro to move a cell up when a cell was removed to another sheet.

Participate now!

Share