PDA

View Full Version : Solved: Move row based on text



karrims
11-15-2007, 03:46 PM
Hi All,
I am less than a beginner with VBA and I am trying to do the following:

When the word "filled" is entered in a cell on sheet A, I want that entire row to move to sheet B, next blank row available and delete the blank row left on sheet A. I don't even know where to begin.:dunno

Thanks in advance!

Karri

Reafidy
11-15-2007, 04:05 PM
Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
If Target.Value <> "filled" Then Exit Sub
With Worksheets("Sheet2")
On Error Resume Next
lRow = .Cells.Find(What:="*", After:=.[A1], SearchDirection:=xlPrevious).Row + 1
If Err.Number <> 0 Then lRow = 1
Application.EnableEvents = False
Target.EntireRow.Cut .Cells(lRow, 1) ' opps - cut is better
Application.EnableEvents = True
End With
End Sub

Bob Phillips
11-15-2007, 04:42 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NextRow As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Columns(1)) Is Nothing Then

If Target.Value = "filled" Then

With Worksheets("Sheet2")
NextRow = .Range("A1").End(xlUp).Row
If NextRow = 1 And .Cells(NextRow, "A").Value = "" Then
Else
NextRow = NextRow + 1
End If
Target.EntireRow.Cut .Cells(NextRow, "A")
End With
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub

Reafidy
11-15-2007, 05:03 PM
On erroro GoTo ws_exit


Typo: On Erroro

karrims
11-16-2007, 12:35 PM
You're my heros! :bigkiss: Thank you so much! That's perfect! </IMG>