+ Reply to Thread
Results 1 to 4 of 4

Move rows only does the first one

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    06-12-2007
    Posts
    144

    Move rows only does the first one

    I have this sub that is supposed to move any rows with 'ERROR' in column 33 to a new row in the ERRORS sheet

    For some reason it only does the first one and i cant figure out why.
    It also doesnt delete the row it has cut from in the IMPORT sheet.

    Can anyone see where the problem is?
    Or perhaps a better way of doing it?

    'move all rows with 'ERROR' in column 33 to the ERRORS sheet
    
       Sheets("ERRORS").Select
       lastrowE = ActiveSheet.UsedRange.Rows.Count + 1
       Sheets("ImportSheet").Select
       lastrowI = ActiveSheet.UsedRange.Rows.Count
       firstrowI = 5
    
    For x = firstrowI To lastrowI
    If Left(Cells(x, 33), 5) = "ERROR" Then
        Sheets("ImportSheet").Select
        Rows(x & ":" & x).Select
        Application.CutCopyMode = False
        Selection.Cut
        Sheets("ERRORS").Select
        Rows(lastrowE & ":" & lastrowE).Select
        ActiveSheet.Paste
        lastrowE = lastrowE + 1
    End If
    Next x
    Attached Files Attached Files

  2. #2
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    You need to work from bottom to top, so that moving a row doesn't disturb the rows you haven't yet inspected.

    For x = lastrowI To firstrowI Step -1

  3. #3
    Forum Contributor
    Join Date
    06-12-2007
    Posts
    144
    good point

    but its still only doing one...

  4. #4
    Forum Contributor
    Join Date
    06-12-2007
    Posts
    144
    i got it working now...somehow

    'move all rows with errors to the errors sheet
       Sheets("ImportSheet").Select
       lastrowE = Sheets("ERRORS").UsedRange.Rows.Count + 1
       lastrowI = Sheets("ImportSheet").UsedRange.Rows.Count
       firstrowI = 5
    For x = lastrowI To firstrowI Step -1
        If Cells(x, 35) <> "" Then
        Rows(x & ":" & x).Select
        Application.CutCopyMode = False
        Selection.Cut
        Sheets("ERRORS").Select
        Rows(lastrowE & ":" & lastrowE).Select
        ActiveSheet.Paste
        lastrowE = lastrowE + 1
        Sheets("ImportSheet").Select
        Rows(x & ":" & x).EntireRow.Delete
        End If
    Next x

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1