Auto Hide Rows

[ExcelRoy]

Hi,

I am looking for some code to implement into a sheet

I would like to auto hide a row if in column "H" it shows "Inactive"

This would be within the ranges A1:H100

Many Thanks

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H1:H100")) Is Nothing Then Exit Sub
Target.EntireRow.Hidden = Target.Value = "Inactive"
End Sub

 

[Zaf]

Here is another way to look at it.

Though code above does seem more short and sweet

Public Sub FinIna()
 
Dim i As Integer
Range("A1").Select

Do While ActiveCell.Address <> Range("I1").Address
     For i = 1 To 100
          If UCase(Trim(ActiveCell.Offset(i - 1, 0).Value)) = "INACTIVE" Then
          Debug.Print (ActiveCell.Offset(i - 1, 0).Value)
          Selection.EntireColumn.Hidden = True
          End If
    Next i

ActiveCell.Offset(0, 1).Select
Loop
 
End Sub

 

[ExcelRoy]

Hi Guys,

Are both solution supposed to be automatic?

It doesn't seem to work?

Thanks

 

[Zaf]

I've tested in a separate workbook and the code does work automatically.

What is the error message that you are getting?

Could you possibly post a screen shot of what the sheet looks like?

 

[VoG]

Does column H contain formulas?

 

[ExcelRoy]

Hi,

No error message just not working

yes VoG, formulas

 

[VoG]

Try replacing my previous code with

Private Sub Worksheet_Calculate()
Dim i As Long
Application.ScreenUpdating = False
For i = 1 To 100
    Rows(i).Hidden = Range("H" & i).Value = "Inactive"
Next i
Application.ScreenUpdating = True
End Sub

 

[ExcelRoy]

Sorry VoG , still nothing

 

[Zaf]

Ha looks like I should have read your question better