Macro or Event Code to Hide rows if value in cell = No & unhide if Cell = Yes

[Michelledryr]

Good Day all,

I have very limited knowledge on Marcos/ Event codes. I would really appreciate help with the below.

I have a workbook with 2 Sheets. Sheet 1 (Form) Sheet 2 (Result).

Sheet 1 contains multiple yes & no questions which kicks out a result on the Result sheet.

I require a macro or event code which will run automatically based on the below.

If sheet 1 (Form) B17 = No Sheet 2 (Results) row 43 - Hidden if Yes unhide
If sheet 1 (Form) B18 = No Sheet 2 (Results) row 44 - Hidden if Yes unhide
If sheet 1 (Form) B19 = No Sheet 2 (Results) row 45 - Hidden if Yes unhide
If sheet 1 (Form) B20 = No Sheet 2 (Results) row 47 - Hidden if Yes unhide
If sheet 1 (Form) B21 = No Sheet 2 (Results) row 50 - Hidden if Yes unhide
If sheet 1 (Form) D17 = No Sheet 2 (Results) row 46 - Hidden if Yes unhide
If sheet 1 (Form) D18 = No Sheet 2 (Results) row 38 - Hidden if Yes unhide
If sheet 1 (Form) D19 = No Sheet 2 (Results) row 49 - Hidden if Yes unhide
If sheet 1 (Form) D21 = No Sheet 2 (Results) row 48 - Hidden if Yes unhide

I would really appreciate if anyone is able to help me.

Thanking you in advance for your assistance.

 

[GJSBED]

So you want to basically just copy and paste all the Yes answers from one page to another with a macro?

begs the question Why...



You can achieve this by just recording yourself doing it once with the macro recorder

filter the page to 'yes' in the column then copy and paste it all to the other.

 

[wideboydixon]

Right click on the "Form" sheet name, select "View Code" and paste the following:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim changedCell As Range
Dim resultsRow As Long

For Each changedCell In Target
    resultsRow = 0
    Select Case changedCell.Address
        Case "$B$17"
            resultsRow = 43
        Case "$B$18"
            resultsRow = 44
        Case "$B$19"
            resultsRow = 45
        Case "$B$20"
            resultsRow = 47
        Case "$B$21"
            resultsRow = 50
        Case "$D$17"
            resultsRow = 46
        Case "$D$18"
            resultsRow = 38
        Case "$D$19"
            resultsRow = 49
        Case "$D$21"
            resultsRow = 48
    End Select
    
    If resultsRow <> 0 Then
        Sheets("Results").Rows(resultsRow).EntireRow.Hidden = (changedCell.Value = "No")
    End If
Next changedCell

End Sub

WBD

 

[Michelledryr]

Good Day Wideboydixon,

Thank you so much for your help. I am however getting an error "Ambiguous name". I assume it is due to another code I have on the sheet with the same name. Also the result sheet name had to be changed to "Sea Export FCL". Will you kindly assist me to edit the above given code to incorporate the below code I have on the Form sheet

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$B$13" Then
Dim b13val As String

b13val = Target.Value

If b13val = "FOB" Then
Sheets("Sea Export FCL").Range("54:57,63:71").EntireRow.Hidden = True
ElseIf b13val = "CFR" Or b13val = "CIF" Then
Sheets("Sea Export FCL").Rows("63:71").Hidden = True
Else
Sheets("Sea Export FCL").Rows.Hidden = False
End If
End If


End Sub

I really appreciate you assistance with this matter

 

[wideboydixon]

I think this should get you through:

Private Sub Worksheet_Change(ByVal Target As Range)

Const RESULTS_SHEET_NAME = "Sea Export FCL"
Dim changedCell As Range
Dim resultsRow As Long

For Each changedCell In Target
    resultsRow = 0
    Select Case changedCell.Address
        Case "$B$13"
            Sheets(RESULTS_SHEET_NAME).Rows("63:71").Hidden = (changedCell.Value = "FOB" Or changedCell.Value = "CFR" Or changedCell.Value = "CIF")
            Sheets(RESULTS_SHEET_NAME).Rows("54:57").Hidden = (changedCell.Value = "FOB")
        Case "$B$17"
            resultsRow = 43
        Case "$B$18"
            resultsRow = 44
        Case "$B$19"
            resultsRow = 45
        Case "$B$20"
            resultsRow = 47
        Case "$B$21"
            resultsRow = 50
        Case "$D$17"
            resultsRow = 46
        Case "$D$18"
            resultsRow = 38
        Case "$D$19"
            resultsRow = 49
        Case "$D$21"
            resultsRow = 48
    End Select
    
    If resultsRow <> 0 Then
        Sheets(RESULTS_SHEET_NAME).Rows(resultsRow).EntireRow.Hidden = (changedCell.Value = "No")
    End If
Next changedCell

End Sub

WBD

 

[Michelledryr]

Hi WBD,

Thank you once again for your kind assistance.

The first part of the code works perfectly.

The No/Yes Hide/unhide does not work though. Could it be because cells in on the form sheet has a dropdown (Data Validation list) in with a Yes/ No option?

Once again thank you so much for your assistance.

 

[wideboydixon]

Strange. All working as expected here. Is there a chance that the "No" value has an extra space on it or something that makes it not exactly equal to "No". Try putting the following into the immediate window in the VBA Editor (Ctrl+G gets to the immediate window):

? "|" & Sheets("Form").Range("B18").Value & "|"

Does the result look like this:

|No|

?

WBD

 

[Michelledryr]

Hi WBD,

Thank you so much for your assistance. I just had to change the NO to capitals.

Hope you had a lovely day further