Monday, November 14, 2011

Delete all rows except particular rows in Excel 2007


Option Explicit
Sub DeleteRows()
    'ask the user for an input string
    Dim srchString
    srchString = InputBox("Preserve rows that begin with..")
    'add asterisk at the end for - begins with condition
    srchString = srchString + "*"
    Dim i As Long
    i = 1
    Dim countEmptyRows As Long
    countEmptyRows = 1
    'loop till 10 consecutive empty cells are found
    Do Until countEmptyRows = 10
        If Not (Cells(i, 1).Value Like srchString) Then
            'we cannot directly delete the row, buddy
            If Trim(Cells(i, 1)) = "" Then
                'Start counting blank lines
                countEmptyRows = countEmptyRows + 1
            Else
                'Non Blank line found
                countEmptyRows = 0
            End If
            'delete the row anyway
            Cells(i, 1).EntireRow.Delete
            'no any increment for i - cause this cell is being deleted
        Else
            i = i + 1
        End If
    Loop
End Sub