Excel VBA AND <>

[CIRonin]

Hey all
I have some code that hides rows if a value in a column is = "TERMDT" which works great... But I am trying to get some working that has a few criteria
What they asked for is "IF column Z (new status) is W, hide/remove UNLESS column D (BDCA code) is MMJ, MBJ, MNN, MBN, MML, MMP
"
The first bit is
<code>
Sub HideRows1()
BeginRow = 1
EndRow = 1000
ChkCol = ("BX")


For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "TERMDT" Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub
</code>

The second bit that I can't get to work yet is...
<code>
Sub HideRows2()
BeginRow = 1
EndRow = 1000
ChkCol = ("Z")
ChkColB = ("D")


For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value = "W" Then
Cells(RowCnt, ChkColB).Value <>"MMJ" AND
Cells(RowCnt, ChkColB).Value <>"MBJ" AND
Cells(RowCnt, ChkColB).Value <> "MNN" AND
Cells(RowCnt, ChkColB).Value <> "MBN" AND
Cells(RowCnt, ChkColB).Value <> "MML" AND
Cells(RowCnt, ChkColB).Value <> "MMP" AND
Then


Cells(RowCnt, ChkCol).EntireRow.Hidden = True
End If
Next RowCnt
End Sub
</code>
Any suggestions for a VBA novice?
Thanks!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CIRonin()
   Dim Cl As Range
   Dim Ary As Variant
   
   Ary = Array("MMJ", "MBJ", "MNN", "MBN", "MML", "MMP")
   For Each Cl In Range("Z2", Range("Z" & Rows.Count).End(xlUp))
      If Cl = "W" Then
         If IsError(Application.Match(Cl.Offset(, -22), Ary, 0)) Then Cl.EntireRow.Hidden = True
      End If
   Next Cl
End Sub

 

[CIRonin]

That worked like a charm!
Thanks!

Hi & welcome to MrExcel.
How about

Sub CIRonin()
   Dim Cl As Range
   Dim Ary As Variant
   
   Ary = Array("MMJ", "MBJ", "MNN", "MBN", "MML", "MMP")
   For Each Cl In Range("Z2", Range("Z" & Rows.Count).End(xlUp))
      If Cl = "W" Then
         If IsError(Application.Match(Cl.Offset(, -22), Ary, 0)) Then Cl.EntireRow.Hidden = True
      End If
   Next Cl
End Sub

 

[Fluff]

You're welcome & thanks for the feedback