Results 1 to 3 of 3

Thread: Combine two Subs into one
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Combine two Subs into one

    Hi all.

    I have two private subs, they work when i run them alone but i am having trouble combining them

    The function of the code is id you select a value in d15 it would unhide rows depending on the value so select the number one and it hides the 14 rows below

    Then move onto d16 and the axact same principal applies

    First code

    If Intersect(Target, Range("D15")) Is Nothing Or Target.Cells.Count > 20 Then
    Exit Sub

    ElseIf Range("D15").Value = "15" Then
    Rows("25:39").EntireRow.Hidden = False


    ElseIf Range("D15").Value = "1" Then
    Rows("25").EntireRow.Hidden = False
    Rows("26:39").EntireRow.Hidden = True

    End If


    End Sub

    Second Code


    if Intersect(Target, Range("D16")) Is Nothing Or Target.Cells.Count > 20 Then
    Exit Sub

    ElseIf Range("D15").Value = "D16" Then
    Rows("41:57").EntireRow.Hidden = False


    ElseIf Range("D16").Value = "1" Then
    Rows("41").EntireRow.Hidden = False
    Rows("42:57").EntireRow.Hidden = True


    End If


    End Sub

    I would greatly appreciate any help

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,194
    Post Thanks / Like
    Mentioned
    67 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Combine two Subs into one

    According to the logic of the first code, it should be like this in the second code, but check the data in red.


    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'First Code
      If Target.Count > 20 Then Exit Sub
      If Not Intersect(Target, Range("D15")) Is Nothing Then
        If Range("D15").Value = "15" Then
          Rows("25:39").EntireRow.Hidden = False
        ElseIf Range("D15").Value = "1" Then
          Rows("25").EntireRow.Hidden = False
          Rows("26:39").EntireRow.Hidden = True
        End If
      End If
    'Second Code
      If Not Intersect(Target, Range("D16")) Is Nothing Then
        If Range("D16").Value = "16" Then
          Rows("41:57").EntireRow.Hidden = False
        ElseIf Range("D16").Value = "1" Then
          Rows("41").EntireRow.Hidden = False
          Rows("42:57").EntireRow.Hidden = True
        End If
      End If
    End Sub
    Regards Dante Amor

  3. #3
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,031
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Combine two Subs into one

    I question the validity of the statement in red font. Looks like it might be missing 'Range(? )'.

    Code:
    If Intersect(Target, Range("D15:D16")) Is Nothing Or Target.Cells.Count > 20 Then
        Exit Sub
    ElseIf Range("D15").Value = "15" Then
       Rows("25:39").EntireRow.Hidden = False
    
    ElseIf Range("D15").Value = "1" Then
        Rows("25").EntireRow.Hidden = False
        Rows("26:39").EntireRow.Hidden = True
    
    ElseIf Range("D15").Value = "D16" Then
        Rows("41:57").EntireRow.Hidden = False
    
    ElseIf Range("D16").Value = "1" Then
        Rows("41").EntireRow.Hidden = False
        Rows("42:57").EntireRow.Hidden = True
    
    End If
    
    End Sub
    Last edited by JLGWhiz; Aug 21st, 2019 at 07:16 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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