Move row to the bottom of the list

**hawkcs** · 06-12-2007, 03:37 PM

I want the moment that I insert a number >=12 on a cell for the complete row to move to the bottom of the list to the next available empty row.
How can this be done?
---cjs

**VBA Noob** · 06-12-2007, 03:55 PM

Maybe an event macro which goes in the workbook module (See link)

I've assumed you only want this to happen in 1 col. I've picked Col C. Just enter a value in say C3 and if you have rows complete below that it will move to the last row

http://www.contextures.com/xlvba01.html#Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
Dim last As Long
last = Range("C65536").End(xlUp).Row
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Value > = 12 Then
Rows(Target.Row & ":" & Target.Row).Cut
Rows(last + 1 & ":" & last + 1).Insert Shift:=xlDown
End If
End If

End Sub

or

Private Sub Worksheet_Change(ByVal Target As Range)
Dim last As Long
last = Range("C65536").End(xlUp).Row
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("C:C")) Is Nothing Then
If Target.Value > 12 Then
Rows(last + 1 & ":" & last + 1).Value = Rows(Target.Row & ":" & Target.Row).Value
Rows(Target.Row & ":" & Target.Row).Clear
End If
End If

End Sub

VBA Noob

**hawkcs** · 06-12-2007, 05:33 PM

How do I do that...can you please email me a spreadsheet with it already in it??

**VBA Noob** · 06-12-2007, 05:35 PM

See link

VBA Noob

**hawkcs** · 06-12-2007, 07:57 PM

Thank in advance it is working...kindof.

It will move the data...if any number in any column is over 12 also it dumps the row about 10 or 15 row below. How do I make it for 1 specific column or selective column? How do I have the macro constantly run? How do I force it to the line below the lowest?
Thank you again in advance.