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 

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies