Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: moving a row upward in excel

    Hi,

    I am trying to loop through the values in sheet4 and clear the row that does not match the criteria and then select the next row. if that row matches the criteria, then select that row and move it up one row. I am able to loop through all the row and clear the rows that do not match the crieteria but I can't seem to be able to move the rows upward, especially it the first row has no value and it is the first row. Can anyone help??

    RI 8/13/2004 8 2 0 0 0:16:53 3:54:22 3:05:38
    HO 8/13/2004 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00
    JU 8/13/2004 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00
    GA 8/13/2004 8 1 0 0 0:16:28 3:31:52 2:28:08
    LE 8/13/2004 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00
    JE 8/13/2004 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00
    NA 8/13/2004 0 0 0 0 0:00:00 0:00:00 0:00:00 0:00:00




    Sub eleminate()
    Dim vrange As Variant
    Dim irowcount As Integer
    vrange = Sheet4.Range("A1:J11")
    irowcount = UBound(vrange)
    Dim a As Integer
    Sheet4.Activate
    For a = 1 To irowcount
    If Sheet4.Range("C" & a).Value = 0 And Sheet4.Range("D" & a).Value = 0 And Sheet4.Range("E" & a).Value = 0 Then
    Sheet4.Range("A" & a & ":J" & a).Select
    Sheet4.Range("A" & a & ":J" & a).Clear
    'End If
    ElseIf Sheet4.Range("C" & a).Value <> 0 Then
    Range("A" & a & ":J" & a).Select
    a = a - 1
    Range("A" & a & ":J" & a).Cut Destination:=Range("A" & a & ":J" & a)
    End If
    Next a
    Sheet1.Activate
    End Sub

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Unless you have data in colums to the right of your range you can delete the entire row. By deleting the entire row you eliminate having to move following rows up to fill the blank deleted range. Deleting rows changes the bounds of your used range which can be a problem when done in a For Loop. To avoid this problem you can 'Step -1' to loop from the last row of your range to the first. This avoids the row number sequence problem caused by deleting a rows within a For Loop.

    If you have data to the right of your data area you can use Range Delete shift XL Up to delete a specified area.

    In this example I simplified the process by using 'With Activesheet' and '.Cells(r,c)' I just added the values of Cells in colums C,D,E to check the criteria, easy enough assuming you will always be checking for numeric values in these cells.

    Code:
    Sub eleminate1()
        Dim irowcount As Integer
        Dim r As Integer
        Dim i As Integer
        Dim lngAddCDE As Long
             
    '    Sheet4.Activate 'Activate sheet if not called from the active sheet
        With ActiveSheet
        irowcount = .UsedRange.Rows.Count
        
        For r = irowcount To 1 Step -1
            lngAddCDE = 0
            For i = 3 To 6
                If IsNumeric(.Cells(r, i)) Then
                    lngAddCDE = lngAddCDE + .Cells(r, i)
                End If
            Next i
            If lngAddCDE = 0 Then
                .Rows(r).EntireRow.Delete
            End If
        Next
        End With
    
    End Sub
    Last edited by savbill; 08-14-04 at 00:01.
    ~

    Bill

Posting Permissions

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