Home

Hide Rows Help

%3CLINGO-SUB%20id%3D%22lingo-sub-490023%22%20slang%3D%22en-US%22%3EHide%20Rows%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-490023%22%20slang%3D%22en-US%22%3E%3CP%3EDear%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20hide%20rows%20on%20certain%20conditions.%20These%20conditions%20being%20that%20is%20the%20Date%20in%20a%20particular%20row%20is%20in%20the%20past%20and%20an%20adjacent%20cell%20has%20zero%20value%20then%20hide%20the%20entire%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wrote%20the%20macro%20below%20which%20works%20if%20a%20type%20hide%20or%20unhide%20in%20Cell%20AI.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%3CBR%20%2F%3EActiveSheet.Activate%3CBR%20%2F%3EIf%20Not%20Application.Intersect(Range(%22A1%22)%2C%20Range(Target.Address))%20Is%20Nothing%20Then%3CBR%20%2F%3ESelect%20Case%20Target.Value%3CBR%20%2F%3ECase%20Is%20%3D%20%22Hide%22%3A%20Rows(%225%3A10%22).EntireRow.Hidden%20%3D%20True%3CBR%20%2F%3ECase%20Is%20%3D%20%22Unhide%22%3A%20Rows(%225%3A10%22).EntireRow.Hidden%20%3D%20False%3CBR%20%2F%3EEnd%20Select%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20I%20have%20tried%20to%20tweak%20it%20to%20basically%20say%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECase%20if(Range%20of%20Cells%20(B5%3AB10)%20is%20%26lt%3B%3D%20TODAY()%20AND%20Cells%20(E5%3AE10)%20%3D%200%2C%20EntireRow.Hidden%20%3D%20True%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20a%20few%20variation%20in%20syntacs%20but%20I%20do%20not%20know%20the%20correct%20way%20to%20construct%20the%20code.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVonryan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-490023%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-490106%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20Rows%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-490106%22%20slang%3D%22en-US%22%3Echange%20this%20code%3CBR%20%2F%3ECase%20if(Range%20of%20Cells%20(B5%3AB10)%20is%20%26lt%3B%3D%20TODAY()%20AND%20Cells%20(E5%3AE10)%20%26gt%3B%200%2C%20EntireRow.Hidden%20%3D%20True%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-490129%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20Rows%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-490129%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329046%22%20target%3D%22_blank%22%3E%40aazirasif%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20response.%20Please%20advise%20what%20to%20change%20it%20too%20as%20I%20do%20not%20know.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-497279%22%20slang%3D%22en-US%22%3ERe%3A%20Hide%20Rows%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-497279%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20manged%20to%20solve%20the%20mystery%20myself%20after%20a%20lot%20of%20searching.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20eventually%20learnt%20how%20to%20place%20buttons%20on%20the%20spreadsheet%20and%20trigger%20VBA%20functions.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20original%20code%20I%20used%20was%20good%20apart%20from%20the%20OFFSET.%20I%20thought%20OFFSET%20parameters%20were%20Horizontal%2C%20Vertical.%20As%20it%20happens%20it%20is%20the%20other%20way%20around.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20my%20code%20now%20and%20everything%20if%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESub%20Hide()%3CBR%20%2F%3EDim%20LastRow%20As%20Long%2C%20c%20As%20Range%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3ELastRow%20%3D%20Cells(Cells.Rows.Count%2C%20%22I%22).End(xlUp).Row%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EFor%20Each%20c%20In%20Range(%22B5%3AB348%22)%3CBR%20%2F%3EIf%20(c.Value%20%26lt%3B%20Date%20And%20c.Offset(0%2C%203).Value%20%3D%200)%20Then%3CBR%20%2F%3Ec.EntireRow.Hidden%20%3D%20True%3CBR%20%2F%3EElseIf%20c.Value%20%26gt%3B%3D%20Date%20Then%3CBR%20%2F%3Ec.EntireRow.Hidden%20%3D%20False%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20True%3CBR%20%2F%3EEnd%20Sub%3CBR%20%2F%3ESub%20Unhide()%3CBR%20%2F%3EDim%20LastRow%20As%20Long%2C%20c%20As%20Range%3CBR%20%2F%3EApplication.EnableEvents%20%3D%20False%3CBR%20%2F%3ELastRow%20%3D%20Cells(Cells.Rows.Count%2C%20%22I%22).End(xlUp).Row%3CBR%20%2F%3EOn%20Error%20Resume%20Next%3CBR%20%2F%3EFor%20Each%20c%20In%20Range(%22B5%3AB348%22)%3CBR%20%2F%3Ec.EntireRow.Hidden%20%3D%20False%3CBR%20%2F%3ENext%3CBR%20%2F%3EOn%20Error%20GoTo%200%3CBR%20%2F%3EEnd%20Sub%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
vonryan
Contributor

Dear All,

 

I am trying to hide rows on certain conditions. These conditions being that is the Date in a particular row is in the past and an adjacent cell has zero value then hide the entire row.

 

I wrote the macro below which works if a type hide or unhide in Cell AI.

 

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Activate
If Not Application.Intersect(Range("A1"), Range(Target.Address)) Is Nothing Then
Select Case Target.Value
Case Is = "Hide": Rows("5:10").EntireRow.Hidden = True
Case Is = "Unhide": Rows("5:10").EntireRow.Hidden = False
End Select
End If
End Sub

 

But I have tried to tweak it to basically say

 

Case if(Range of Cells (B5:B10) is <= TODAY() AND Cells (E5:E10) = 0, EntireRow.Hidden = True

 

I tried a few variation in syntacs but I do not know the correct way to construct the code.

 

Please help.

 

Vonryan

3 Replies
change this code
Case if(Range of Cells (B5:B10) is <= TODAY() AND Cells (E5:E10) > 0, EntireRow.Hidden = True

Hi @aazirasif 

 

Thank you for your response. Please advise what to change it too as I do not know.

Hi All,

 

I have manged to solve the mystery myself after a lot of searching.

 

I eventually learnt how to place buttons on the spreadsheet and trigger VBA functions.

 

The original code I used was good apart from the OFFSET. I thought OFFSET parameters were Horizontal, Vertical. As it happens it is the other way around.

 

This is my code now and everything if fine.

 

Sub Hide()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "I").End(xlUp).Row
On Error Resume Next
For Each c In Range("B5:B348")
If (c.Value < Date And c.Offset(0, 3).Value = 0) Then
c.EntireRow.Hidden = True
ElseIf c.Value >= Date Then
c.EntireRow.Hidden = False
End If
Next
On Error GoTo 0
Application.EnableEvents = True
End Sub
Sub Unhide()
Dim LastRow As Long, c As Range
Application.EnableEvents = False
LastRow = Cells(Cells.Rows.Count, "I").End(xlUp).Row
On Error Resume Next
For Each c In Range("B5:B348")
c.EntireRow.Hidden = False
Next
On Error GoTo 0
End Sub