Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

**DocAElstein** · 09-16-2015, 06:44 PM

In support of answer to this post
https://excel.tips.net/T001940_Hidin...ell_Value.html

2020-06-19 11:13:59
Erica
This is my script:

Code:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 3 And Target.Row = 5 Then If Target.Value = "Yes" Then Application.Sheets("FAR").Select Application.Rows("14").Select Application.Selection.EntireRow.Hidden = False Application.Sheets("Additions").Select Application.Rows("6").Select Application.Selection.EntireRow.Hidden = False ElseIf Target.Value = "No" Then Application.Sheets("FAR").Select Application.Rows("14").Select Application.Selection.EntireRow.Hidden = True Application.Sheets("Additions").Select Application.Rows("6").Select Application.Selection.EntireRow.Hidden = True End If End If End Sub

How do I make this continue for the next 19 rows?

The idea is to:
hide row 14 in Tab FAR and Row 7 in Tab Additions if the value in row 6 Tab Additions is "NO" and leave unhidden if yes
hide row 15 in Tab FAR and Row 8 in Tab Additions if the value in row 7 Tab Additions is "NO" and leave unhidden if yes
hide row 16 in Tab FAR and Row 9 in Tab Additions if the value in row 8 Tab Additions is "NO" and leave unhidden if yes

and so fort 20 times

Please can you help me with the reloop of this script ........

Hello Erica
I expect you may have got your macro using the macro recorder? Using the macro recorder is a very good idea as it can easily get you your required syntax and initial coding.
Often we can simplify the macro given by the recorder, in particular bearing in mind that VBA does not need to select or activate things as we do. We have to click on a cell, or row, for example in order to do anything with it. That results in code lines involving Activateing and Selecting. VCBA does not need those actions. Bearing this in mind, and making a few other minor modifications I can simplify your given macro to

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 3 And Target.Row = 5 Then
        If Target.Value = "Yes" Then
         Let Worksheets("FAR").Rows("14").Hidden = False
         Let Worksheets("Additions").Rows("6").Hidden = False
        ElseIf Target.Value = "No" Then
         Let Worksheets("FAR").Rows("14").Hidden = True
         Let Worksheets("Additions").Rows("6").Hidden = True
        End If
    End If
End Sub

Further to simplify, we can add an initial code line that will limit the most of the macro to running only when a change occurs in the range of interest, which , if I am correct is range C5 to C24 in worksheets Additions
After this, we no longer need to have the check for the column 3
So now we have this

Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Application.Intersect(Target, Me.Range("C5:C24")) Is Nothing Then Exit Sub
    If Target.Row = 5 Then        '       Target.Column = 3 And Target.Row = 5 Then
        If Target.Value = "Yes" Then
         Let Worksheets("FAR").Rows("14").Hidden = False
         Let Worksheets("Additions").Rows("6").Hidden = False
        ElseIf Target.Value = "No" Then
         Let Worksheets("FAR").Rows("14").Hidden = True
         Let Worksheets("Additions").Rows("6").Hidden = True
        End If
    End If
End Sub

Thread: Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

Thread Tools

Display

Threaded View

Similar Threads

Appendix Thread 2. ( Codes for other Threads, HTML Tables, etc.)

Appendix Thread. ( Codes for other Threads, ( Avinash ).)

Appendix Thread. Diet Protokol Coding Adaptions

Restrict data within the Cell (Data Validation)

Bookmarks

Bookmarks

Posting Permissions