+ Reply to Thread
Results 1 to 7 of 7

Deleting rows NOT containing defined values

Hybrid View

  1. #1
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Deleting rows NOT containing defined values

    Hi,
    following macro published by ron de bruin deletes all rows in a table where a cell contains a specific value in a given column.
    The specific values are contained in an array like
    myStrings = Array("Q8", "SUPP")
    But in fact, the number of elements of the array could increase in time.
    So I would like to adapt the macro so that it deletes all rows NOT containing given values ( they wil not vary in time)

    As I am completely novice in VBA, I would appreciate your help very much

    Sub Find_Example()
        Dim calcmode As Long
        Dim ViewMode As Long
        Dim myStrings As Variant
        Dim FoundCell As Range
        Dim I As Long
        Dim myRng As Range
        Dim sh As Worksheet
    
        With Application
            calcmode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
    
        'We use the ActiveSheet but you can also use Sheets("MySheet")
        Set sh = Sheets("A")
    
        'We search in column A in this example
        Set myRng = sh.Range("AJ:AJ")
    
        'Add more search strings if you need
        myStrings = Array("Q8", "SUPP")
    
    
        With sh
    
            'We select the sheet so we can change the window view
            .Select
    
            'If you are in Page Break Preview Or Page Layout view go
            'back to normal view, we do this for speed
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
    
            'Turn off Page Breaks, we do this for speed
            .DisplayPageBreaks = False
    
            'We will search the values in MyRng in this example
            With myRng
    
                For I = LBound(myStrings) To UBound(myStrings)
                    Do
                        Set FoundCell = myRng.Find(What:=myStrings(I), _
                                                   After:=.Cells(.Cells.Count), _
                                                   LookIn:=xlFormulas, _
                                                   LookAt:=xlWhole, _
                                                   SearchOrder:=xlByRows, _
                                                   SearchDirection:=xlNext, _
                                                   MatchCase:=False)
                        'Use xlPart If you want to search in a part of the FoundCell
                        'If you use LookIn:=xlValues it will also delete rows with a
                        'formula that evaluates to "Ron"
                        If FoundCell Is Nothing Then
                            Exit Do
                        Else
                            FoundCell.EntireRow.Delete
                        End If
                    Loop
                Next I
    
            End With
    
        End With
    
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = calcmode
        End With
    
    End Sub


    Thanks in advance

    André

  2. #2
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Change column A to suit
    Change Value1, Value2 etc to suit

    Sub DelRows()
       Dim l4Row As Long
       
       For l4Row = Cells(Rows.Count, "a").End(xlUp).Row To 1 Step -1
          Select Case Cells(l4Row, "a").Value
          Case "Value1", "Value2", "Value3"
             'need to keep this entry - do nothing
          Case Else
             Rows(l4Row).Delete
          End Select
       Next l4Row
    End Sub
    Please Read Forum Rules Before Posting
    Wrap VBA code by selecting the code and clicking the # icon or Read This
    How To Cross Post politely

    Top Excel links for beginners to Experts

    If you are pleased with a member's answer then use the Scales icon to rate it
    If my reply has assisted or failed to assist you I welcome your Feedback.

  3. #3
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Thx Mudraker,
    my first attempt erased everything, not realizing the values were case sensitive.
    If I want to keep the first row sould I use ?
    For l4Row = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1

  4. #4
    Valued Forum Contributor mudraker's Avatar
    Join Date
    11-10-2003
    Location
    Melbourne, Australia
    Posts
    3,983
    Yeap change the 1 to 2

    To eliminate the case sensitve

    use

    Select Case LCase(Cells(l4Row, "a").Value)
    and enter the case values in lower case

    Case "value1", "value2", "value3"

  5. #5
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi mudraker,
    thanks for the input
    I think it will work perfectly and I'll try it Tuesday at work.
    You'll get feedback ASAP

    André

  6. #6
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi Mudraker
    Macro worked great
    Many thx

+ 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