Results 1 to 10 of 10

Find "single row" and delete them

Threaded View

  1. #1
    Registered User
    Join Date
    07-13-2009
    Location
    NYC, USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Find "single row" and delete them

    Hi everyone,
    I am pretty new to Excel - but I have a little programming experience.
    Anyway, I am stuck and could need help.

    I tried to write a script that does following:


    I have data that looks like this:

    1
    1
    2
    2
    3
    3
    4
    5
    5
    6
    6

    As you can see, mostly every two rows contain same values.
    But there are some values (here it is 4) that is a "single row".

    I want to find every "single row" and delete them.
    I tried a long time but reached frustration now
    I hope someone can help me.
    My script is below.
    Thank You!


    Sub DeleteFalse()
    
        Set xRng = Selection
        xCount = xRng.Rows.Count
        Dim blDimensioned As Boolean
        Dim myArray() As Integer
        Dim arrayCounter As Integer
        Dim s As Integer
        arrayCounter = 0
        s = 0
        
        blDimensioned = False
            For i = 3 To xCount
                If blDimensioned = True Then
                    If xRng.Cells(i, 1) = xRng.Cells(i + 1, 1) Then
                        s = 2
                    Else
                        ReDim Preserve myArray(UBound(myArray) + 1)
                        myArray(arrayCounter) = i
                        arrayCounter = arrayCounter + 1
                        s = 1
                    End If
                Else
                    If xRng.Cells(i, 1) = xRng.Cells(i + 1, 1) Then
                        s = 2
                    Else
                        ReDim myArray(0 To 0) As Integer
                        myArray(arrayCounter) = i
                        blDimensioned = True
                        arrayCounter = arrayCounter + 1
                        s = 1
                    End If
                End If
            i = i + s
            Next i
            
    '        For i = 0 To UBound(myArray)
    '            xRng.Cells(i + 1, "e").FormulaArray = "raus"
    '        Next i
    
            For i = 0 To UBound(myArray)
                xRng.Cells(myArray(i)).EntireRow.Delete
            Next i
    End Sub
    Last edited by jk@CU; 07-15-2009 at 06:52 PM.

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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