Hiding Rows Depending On A Cell Value

eddiesvoicebox · #1 (**permalink**) 10-26-04, 03:48

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

DavidCoutts · #2 (**permalink**) 10-26-04, 08:00

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

eddiesvoicebox · #3 (**permalink**) 10-26-04, 09:24

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.

DavidCoutts · #4 (**permalink**) 10-26-04, 10:04

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

Internet Services

Web Development

Digital Marketing

Consumer Tech