Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2004
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VBA Exclusionary Macro (2000)

    I have a report that I receive regularly that, after I am finished running my other macros, I must delete every row EXCEPT where a column has a certain value.
    Right now, the code I'm using is:

    <code>
    Private Sub LicenseCertificationDeleter_Click()
    Dim ans As String
    Dim c As Range
    Set wks = ActiveSheet
    With wks
    col = ActiveCell.Column

    ans = InputBox("Delete the rows in active column where value is:")
    Application.ScreenUpdating = False
    With .Cells(1, col).EntireColumn
    Do
    Set c = .Find(ans, LookIn:=xlValues)
    If Not c Is Nothing Then
    c.EntireRow.Delete
    End If
    Loop While Not c Is Nothing
    End With
    End With
    End Sub
    </code>


    This works fine to delete everything that matches what the user types in (Delete the rows in active column where value is = "ans").
    What I need is the opposite; to delete everything in the active column EXCEPT what the user types in (Delete the rows in active column where value is <> "ans")

    This sounds like the easiest thing in the world, but, for the life of me, I can't get the code to work correctly.

    If any of Woody's Warriors can help me out, I would greatly appreciate it!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VBA Exclusionary Macro (2000)

    Try this version:

    Private Sub LicenseCertificationDeleter_Click()
    Dim ans As String
    Dim lngRow As Long
    Dim lngCol As Long
    ans = InputBox("Delete the rows in active column where value is:")
    Application.ScreenUpdating = False
    With ActiveSheet
    lngCol = ActiveCell.Column
    For lngRow = .Cells.Find(What:="*", SearchDirection:=xlPrevious, _
    SearchOrder:=xlByRows).Row To 1 Step -1
    If .Cells(lngRow, lngCol) <> ans Then
    .Rows(lngRow).Delete
    End If
    Next lngRow
    End With
    Application.ScreenUpdating = True
    End Sub

    It doesn't use Find to locate the input value (although it does use Find to determine the last used row), but loops backwards through the rows.

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: VBA Exclusionary Macro (2000)

    You'll need code such as in <post#=277602>post 277602</post#>, but change the loop like this (untested):

    For I = lLastRow To 0 Step -1
    With wks.Cells(l, col)
    If .Value <> "ans" Then .EntireRow.Delete
    End With
    Next I
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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