• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

delete row if excel 2007 vba

Hello all,

I need your help on a macro.

My problem is with this part of the code:
  varList = VBA.Array("QC", "NS", "FL")

Open in new window


I would like to do:
  varList <> VBA.Array("ON")

Open in new window


But it does not work with <>

How can i fix that?

Thanks for your help


 Dim lngLastRow As Long
    Dim rngToCheck As Range
    Dim varList As Variant
    
    
    Application.ScreenUpdating = False
    
    varList = VBA.Array("QC", "NS", "FL")
    
    With Sheets("Fleet")
        'find the last row in column A
        lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Set rngToCheck = .Range(.Cells(1, 9), .Cells(lngLastRow, 1))
    End With
    
    With rngToCheck
        .AutoFilter _
            Field:=9, _
            Criteria1:=varList, _
            Operator:=xlFilterValues
        
        'assume the first row had headers
        On Error Resume Next
        .Offset(1, 0).Resize(.Rows.Count - 1, 1). _
            SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
        
        'remove the autofilter
        .AutoFilter
    End With

    Application.ScreenUpdating = True

Open in new window

0
Wilder1626
Asked:
Wilder1626
  • 3
  • 3
1 Solution
 
Rory ArchibaldCommented:
If it's just one value, you don't need an array - you can simply specify "<>ON" as the criteria. If it is an array that you want to exclude, you cannot do that - they can only be used inclusively.
0
 
Wilder1626Author Commented:
So you aresaying that it should be like that?

 Application.ScreenUpdating = False
    
    varList "<>ON"
    
    With Sheets("Fleet")
        'find the last row in column A
        lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Set rngToCheck = .Range(.Cells(1, 9), .Cells(lngLastRow, 1))
    End With

Open in new window


If i do that, now i have compilation error Sub, Function or Property required
0
 
Rory ArchibaldCommented:
varList = "<>ON"

Open in new window


then:
   With rngToCheck
        .AutoFilter _
            Field:=9, _
            Criteria1:=varList, _
            Operator:=xlAnd

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Wilder1626Author Commented:
OK,

One last thing please.

I just want to delete what start at row 4 going down since the header is at row 3.

What can i do to fix this?

 Dim lngLastRow As Long
    Dim rngToCheck As Range
    Dim varList As Variant
    
    
    Application.ScreenUpdating = False
    
    varList = "<>ON"
    
    With Sheets("Fleet")
        'find the last row in column A
        lngLastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
        
        Set rngToCheck = .Range(.Cells(1, 9), .Cells(lngLastRow, 1))
    End With
    
    With rngToCheck
        .AutoFilter _
            Field:=9, _
            Criteria1:=varList, _
            Operator:=xlAnd

        
        'assume the first row had headers
        On Error Resume Next
        .Offset(1, 0).Resize(.Rows.Count - 1, 1). _
            SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
        
        'remove the autofilter
        .AutoFilter
    End With

    Application.ScreenUpdating = True

Open in new window


Thanks again
0
 
Rory ArchibaldCommented:
Change line 14 to:
Set rngToCheck = .Range(.Cells(3, 9), .Cells(lngLastRow, 1))

Open in new window

0
 
Wilder1626Author Commented:
Perfect

Thank you so much.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now