Consulting

Results 1 to 10 of 10

Thread: Code to hide row if no value

  1. #1
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location

    Code to hide row if no value

    I have a group of cells merge "C23:I23" which get value from N9 (which has a Vlookup formula) I want row 23 to hide when no return comes to N9.

    Can anyone help?

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    In the worksheet code module, try using the Change event handler


    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If IsError(Me.Range("N9").Value) Then
            Me.Rows(23).Hidden = True
            
        ElseIf Len(Me.Range("N9").Value) = 0 Then
            Me.Rows(23).Hidden = True
    
         Else
            Me.Rows(23).Hidden = False
        
        End If
    
    End Sub
    I did a test where N9 is the result of a VLookup()
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    Quote Originally Posted by Paul_Hossler View Post
    In the worksheet code module, try using the Change event handler


    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        If IsError(Me.Range("N9").Value) Then
            Me.Rows(23).Hidden = True
            
        ElseIf Len(Me.Range("N9").Value) = 0 Then
            Me.Rows(23).Hidden = True
    
         Else
            Me.Rows(23).Hidden = False
        
        End If
    
    End Sub
    I did a test where N9 is the result of a VLookup()
    Hi Paul,

    On your page woks fine but vlookup value comes from a election another sheet. I change the vlookup value for another sheet on you worksheet and realised it also stops working .

    I have a sheet lets call it sheet1 where I have a dropdown box with options, on sheet2 I have the vlookup value (N9) from the drop down o sheet1. On the same sheet2 I have on row 23 a link to the vlookup value from shee2 N9.I need row 23 to hide if no value is selected on Sheet 1 drop down box, thus not populated N9 on sheet2.

    I initially had this code , and it worked but but because I have it on sheets it gave me an error 28 on no stack space:

    Private Sub Worksheet_Calculate()
    Range("A22:A22").EntireRow.Hidden = (Range("N9").Value = "")
    Range("A23:A23").EntireRow.Hidden = (Range("N10").Value = "")
    Range("A24:A24").EntireRow.Hidden = (Range("N11").Value = "")
    End Sub

  4. #4
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Try something like this in the ThisWorkbook code module

    Sheet1 is the one where row 23 is hidden based on N9 on Sheet2 which is where the VLookup() is


    Option Explicit
    
    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
        
        If Not Sh Is Sheet2 Then Exit Sub
        
        If IsError(Sh.Range("N9").Value) Then
            Sheet1.Rows(23).Hidden = True
            
        ElseIf Len(Sh.Range("N9").Value) = 0 Then
            Sheet1.Rows(23).Hidden = True
        Else
            Sheet1.Rows(23).Hidden = False
        End If
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    Some how this code does not work. I am going o explain in better terms hat exactly I need.
    I have a sheet called "Case Files" with 3 dropdown boxes on cells D23, D24 and D25.


    I have another sheet called “Magistrate” with VLOOKUP codes oncells N9,N10,N11 with data from the drop down boxes from Sheet “Case File”.


    Cells N9, N10 and N11 fill in rows 18, 19 and 20respectively.
    What I need is every time any of the drop boxes is empty (Blank)to hide the respective rows (18,19.and 20) on sheet “Magistrate”

    See file attached, although vlookup is no working tis is the idea
    Attached Files Attached Files
    Last edited by Gomesm; 05-02-2018 at 12:17 AM.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    1. I changed your VLookup()s

    2. In the Magestrate code page.


    Option Explicit
    
    Private Sub Worksheet_Calculate()
        Dim i As Long
        Application.EnableEvents = False
        For i = 18 To 20
            Rows(i).Hidden = (Len(Trim(Cells(i, 2).Value)) = 0)
        Next
        Application.EnableEvents = True
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    Quote Originally Posted by Paul_Hossler View Post
    1. I changed your VLookup()s

    2. In the Magestrate code page.


    Option Explicit
    
    Private Sub Worksheet_Calculate()
        Dim i As Long
        Application.EnableEvents = False
        For i = 18 To 20
            Rows(i).Hidden = (Len(Trim(Cells(i, 2).Value)) = 0)
        Next
        Application.EnableEvents = True
    End Sub
    Paul

    Thank you for you help and sheet provided which works perfectly.

    When I change my sheet, including the formulas, it hides all 3 rows and it does not unhide even when selected any field. What am I doing wrong?

    the only thing I change on m vlookup formula was what you have changed, so on mine shows like these:
    =IF('Case Details'!D23="","",VLOOKUP('Case Details'!D23,'Case Details'!$AA:$AI,9,FALSE))

    and on your VBA code I changed it like this:

    Option Explicit
    Private Sub Worksheet_Calculate()
    Dim i As Long
    Application.EnableEvents = False
    For i = 18 To 20
    Rows(i).Hidden = (Len(Trim(Cells(i, 9).Value)) = 0)
    Next
    Application.EnableEvents = True

    End Sub

  8. #8
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    tried with code as you suggested but it doesn't work either. As soon as I make a changes on the dropdown boxes on sheet "Case Files" it hides all 3 rows in "Magistrates" sheet and does not unhide them again.

    Option Explicit

    Private Sub Worksheet_Calculate()
    Dim i As Long
    Application.EnableEvents = False
    For i = 18 To 20
    Rows(i).Hidden = (Len(Trim(Cells(i, 2).Value)) = 0)
    Next
    Application.EnableEvents = True
    End Sub

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Application.Calculate must be on Automatic and Application.EnableEvents must = True. It is turned off in my event macro only for the time to hide or show 3 rows

    The macro used the Calculate event on the Magistrate sheet

    If you delete D24 on the Case Details sheet, the VLookup on the Magistrate sheet in N8 is calculated (and = "") and B19 (=N8 and hence also = "") is calculated

    This triggers the event and if B19 on the Magistrate sheet Len = 0 row 19 on the Magistrate sheet is hidden

    I thought that was the goal??

    If you changed the layout, things might not work

    Look at the revised attachment
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Regular
    Joined
    Apr 2018
    Posts
    13
    Location
    Paul
    Your sheet is absolutely correct and exactly what I need. However when I apply it exactly to mine it hides all 3 rows and does not un-hide it. Not sure what is affecting it.
    Can I please email it so you could kindly have a look, there must b something affecting it. I do not want to post it on he forum as it is an official document.
    Your help is very much appreciated.

Posting Permissions

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