+ Reply to Thread
Results 1 to 3 of 3

Exclude values and delete remaining values

Hybrid View

  1. #1
    Registered User
    Join Date
    07-13-2010
    Location
    Barossa SA, Australia
    MS-Off Ver
    Excel 2007
    Posts
    48

    Exclude values and delete remaining values

    Hi I'm trying to set up a macro that allows me to exclude up 10 different values and then delete all other cells. This is what I have but it will only allow two different values.

        Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
        ActiveSheet.Range("$A$1:$X$50000").AutoFilter Field:=24, Criteria1:="<>BAY" _
            , Operator:=xlAnd, Criteria2:="<>B-G"
        Rows("2:2").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.SpecialCells(xlCellTypeVisible).Select
        Selection.Delete Shift:=xlUp
        ActiveSheet.Range("$A$1:$X$50000").AutoFilter Field:=24
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
    This code is only a small part within a much larger macro. If needed I'm happy to attached the whole lot.

    Cheers BJ5352
    Last edited by BJ5352; 01-11-2011 at 02:37 AM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Exclude values and delete remaining values

    Perhaps adopt an approach along the lines of:

    Sub Example()
        Dim vExceptions As Variant, vData As Variant
        Dim lngData As Long
        Dim xlCalc As XlCalculation
        On Error GoTo Handler
        With Application
            xlCalc = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .enablevents = False
        End With
        vExceptions = Array("A", "B", "C", "D", "E", "F", "G", "I", "J")
        With Sheets("Sheet1")
            With .Range(.Cells(1, "X"), .Cells(.Rows.Count, "X").End(xlUp))
                vData = Application.Transpose(.Value)
                For lngData = LBound(vData) To UBound(vData) Step 1
                    If Not IsNumeric(Application.Match(vData(lngData), vExceptions, 0)) Then
                        vData(lngData) = "#DIV/0!"
                    End If
                Next lngData
                .Value = Application.Transpose(vData)
                On Error Resume Next
                .SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete
                On Error GoTo Handler
            End With
        End With
    ExitPoint:
        With Application
            .Calculation = xlCalc
            .ScreenUpdating = True
            .enablevents = True
        End With
        Exit Sub
        
    Handler:
        MsgBox "Error Has Occurred" & vbLf & vbLf & _
                "Error Number: " & Err.Number & vbLf & vbLf & _
                "Error Desc.: " & Err.Description, _
                vbCritical, _
                "Fatal Error"
        Resume ExitPoint
        
    End Sub
    modify sheet name as per own requirements (set as Sheet1 in the above)

    vExceptions should hold those values that are to be kept.

    the above should be pretty quick to run.

  3. #3
    Registered User
    Join Date
    07-13-2010
    Location
    Barossa SA, Australia
    MS-Off Ver
    Excel 2007
    Posts
    48

    Re: Exclude values and delete remaining values

    Thanks this does the trick.

    Cheers BJ5253

+ 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