Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Unanswered: Hiding Rows Depending On A Cell Value

    Hi, im fairly new to VBA and have a small problem. I have created a worksheet that checks data held in various databases for consistency and accuracy. Depending on the results it would be helpful if i could hide columns that are irrelevant. basically i need to hide any row where a cell in column I has the words "Not In" in it. I have trawled the net so far for a solution and have come up with a couple of bits of code (as below) but with no success so far. Can anyone help at all.

    this is the sort of thing i have found so far:

    If ActiveCell = "Not In" Then Rows(ActiveCell.Row).EntireRow.Hidden = True

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("B2").Value = 0 Then
    Columns(1).Hidden = True
    Else
    Columns(1).Hidden = False
    End If
    End Sub


    Thanks in advance for any help you can offer.

    Regards

    Chris
    christopher.durant@churchill.com

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Is it something like this you want,
    Code:
    Sub HideRows()
        Dim Lastrow As Range
        Dim i As Long
        
        Set Lastrow = Cells.Find("*", , , , , xlPrevious)
        If Not Lastrow Is Nothing Then
            For i = 2 To Lastrow.Row
                If Cells(i, 9).Value = "Not In" Then
                    Cells(i, 9).EntireRow.Hidden = True
                End If
            Next i
        End If
                    
    End Sub
    the code above is only an extension on what you have provided above
    All ive provided is a specific range to look for your value in

    HTH
    Dave

  3. #3
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    Hi, thanks for the speedy reply on this problem. After an eternity of banging my head against the wall after posting this morning one of my teammates came in and with a smug grin set it up for me. I think what i wanted to do was much simpler than what i asked, but for the record here is what i ended up with (in case it is of use to others)

    Sub UpdateMacro()

    'This Macro Unhides All Rows Then Re-Checks For Teams That Were Not In
    Sheets("New Main").Select
    Rows("1:1000").EntireRow.Hidden = False

    'Hides Any Row Where Team Was Not In On That Date
    Application.StatusBar = "Hiding Data For Teams Not In"
    Range("I1000").End(xlUp).Offset(1, 0).Value = "Stop"
    Range("i37").Select
    Do Until ActiveCell.Value = "Stop"
    If ActiveCell = "Not In" Then Rows(ActiveCell.Row).EntireRow.Hidden = True
    ActiveCell.Offset(1, 0).Select
    Loop

    Application.StatusBar = "Data Has Now Been Updated For The Date Specified"

    End Sub


    Once again thanks for the advice.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Couple of quick points about your code,
    Selecting cells in your code is to be avoided if possible it slows things down,
    Also i dont like using things such as Activecell or Activesheet etc. as it can be a nightmare when you try to look back and tell what your codes doing, Especially if it grows bigger than you have there,
    Come back and ask anytime you should have your question answered by the next day,

    Dave

Posting Permissions

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