Consulting

Results 1 to 5 of 5

Thread: Solved: Move row based on text

  1. #1
    VBAX Regular
    Joined
    Nov 2007
    Posts
    9
    Location

    Solved: Move row based on text

    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.

    Thanks in advance!

    Karri

  2. #2
    VBAX Regular
    Joined
    May 2007
    Posts
    18
    Location
    Try:
    [vba]
    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

    [/vba]
    Last edited by Reafidy; 11-15-2007 at 05:05 PM.

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    [vba]

    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
    [/vba]
    Last edited by Bob Phillips; 11-15-2007 at 06:12 PM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Regular
    Joined
    May 2007
    Posts
    18
    Location
    Quote Originally Posted by xld
    [vba]
    On erroro GoTo ws_exit
    [/vba]
    Typo: On Erroro

  5. #5
    VBAX Regular
    Joined
    Nov 2007
    Posts
    9
    Location
    You're my heros! Thank you so much! That's perfect! </IMG>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •