Results 1 to 4 of 4

Thread: Excel VBA AND <>

  1. #1
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Excel VBA AND <>

    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

    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


    The second bit that I can't get to work yet is...

    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

    Any suggestions for a VBA novice?
    Thanks!

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,997
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Excel VBA AND <>

    Hi & welcome to MrExcel.
    How about
    Code:
    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    New Member
    Join Date
    Apr 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Excel VBA AND <>

    That worked like a charm!
    Thanks!

    Quote Originally Posted by Fluff View Post
    Hi & welcome to MrExcel.
    How about
    Code:
    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

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,997
    Post Thanks / Like
    Mentioned
    467 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Excel VBA AND <>

    You're welcome & thanks for the feedback
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •