Results 1 to 3 of 3

Delete row if NOT equal to MULTIPLE Values

This is a discussion on Delete row if NOT equal to MULTIPLE Values within the Excel Questions forums, part of the Question Forums category; Hi! I am having problems deleting multiple rows .... below is the macro to delete rows that is equal to ...

  1. #1
    New Member
    Join Date
    Oct 2006
    Posts
    4

    Default Delete row if NOT equal to MULTIPLE Values

    Hi!

    I am having problems deleting multiple rows .... below is the macro to delete rows that is equal to a specific value. I need one that can delete rows if not equal to specific values. I would really appreciate help on this!

    Thanks

    Dim Firstrow As Long
    Dim Lastrow As Long
    Dim Lrow As Long
    Dim CalcMode As Long
    Dim ViewMode As Long

    With Application
    CalcMode = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    End With

    ViewMode = ActiveWindow.View
    ActiveWindow.View = xlNormalView

    Firstrow = ActiveSheet.UsedRange.Cells(1).Row
    Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1

    With ActiveSheet
    .DisplayPageBreaks = False
    For Lrow = Lastrow To Firstrow Step -1

    If IsError(.Cells(Lrow, "R").Value) Then
    'Do nothing, This avoid a error if there is a error in the cell

    ElseIf .Cells(Lrow, "R").Value = "0" Then .Rows(Lrow).Delete
    'This will delete each row with the Value "0" in Column R, case sensitive.

    End If
    Next
    End With

    ActiveWindow.View = ViewMode
    With Application
    .ScreenUpdating = True
    .Calculation = CalcMode
    End With

  2. #2
    Board Regular bbrnx19's Avatar
    Join Date
    Oct 2006
    Posts
    146

    Default

    hi adavba

    try this code it will delete all rows except where column r = either "0","2" and "4"
    Code:
    Option Explicit
    
    Sub test()
    Dim a()
    Dim R, j, I, r1
    Set R = Columns("r:r")
    R = R.Value2
    
    For j = 1 To Range("r65536").End(xlUp).Row
        Select Case R(j, 1)
        Case "2", "0", "4"
        
        Case Else
            I = I + 1
            ReDim Preserve a(I)
            a(I) = j
        End Select
    Next
    
    Set r1 = Rows(a(1))
    For j = 2 To UBound(a)
         Set r1 = Union(r1, Rows(a(j)))
    Next
    r1.Delete
    End Sub

  3. #3
    Board Regular bbrnx19's Avatar
    Join Date
    Oct 2006
    Posts
    146

    Default

    Post PM
    Hi bbrnx19

    Hope all is well...thanks for your help. It now gets passed Set R = Worksheets("test").Columns("r:r") but get stuck on the below:

    If R(j, 1) = "US" Or R(j, 1) = "UN" Or R(j, 1) = "UQ" Or R(j, 1) = "UR"

    Do you have any ideas?? As always I'm most appreciative of your help.

    Best,

    adamvba

    Sub Deleterows()


    Dim a()
    Dim R, j, I, r1

    Set R = Worksheets("test").Columns("r:r")
    R = R.Value2

    For j = 1 To Range("A65536").End(xlUp).Row
    If R(j, 1) = "US" Or R(j, 1) = "UN" Or R(j, 1) = "UQ" Or R(j, 1) = "UR" Then
    Else
    I = I + 1
    ReDim Preserve a(I)
    a(I) = j
    End If
    Next

    Set r1 = Rows(a(1))
    For j = 2 To UBound(a)
    Set r1 = Union(r1, Rows(a(j)))
    Next
    r1.Delete

    End Sub
    hi adam
    I found if statements will take only two Boolean statement “if a=b or a=c then” you can add multiply if statements but can be confusing

    so I use the Select Case see below


    Code:
    Select case R(j, 1) 
    case  "US" , "UN" ,"UQ" , "UR" 
    
    case Else
           I = I + 1
            ReDim Preserve a(I)
            a(I) = j
     End Select
    will add this to the orginal thread as well

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com