Apr 25 2018
10:59 PM
- last edited on
Jul 25 2018
11:49 AM
by
TechCommunityAP
Apr 25 2018
10:59 PM
- last edited on
Jul 25 2018
11:49 AM
by
TechCommunityAP
Apr 26 2018 01:59 AM
Hi,
If I understand you well, you want to show rows 57 to 72 when you select the value of 1 from the dropdown list, otherwise, you want to hide them.
I think that the dropdown list in cell B3?
Based on that, please try this code instead:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3") = "1" Then
Rows("57:72").EntireRow.Hidden = False
Else
Rows("57:72").EntireRow.Hidden = True
End If
End Sub
Apr 26 2018 06:42 AM
Thanks Haytham, unfortunately it seems like it is my excel sheet that is causing the problem. The same issue remains (that the bars don't get hidden until I type something into cell J3, after which it does not unhide again). I tried your method on a new excel file and it worked. If you happen to know how I can troubleshoot this issue please let me know. Thank you!
Apr 26 2018 07:19 AM
Hi,
Please provide me with screenshots of your dashboard to figure out what the problem is.
It's best if you can attach the workbook.
Jan 11 2020 04:41 PM
Thank you for the info on how to use a macro to hide rows. I have a question as an extension to the first question.
I see how you hide rows based on the content of a cell. Is it also possible to use the same macro to set the print area of the document so that it prints the rows that are still active?
Thanks,
wmsawchuk
Jan 12 2020 10:42 AM
Yes, that's possible!
Try this code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B3") = "1" Then
Rows("10:15").EntireRow.Hidden = False
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$15"
Else
Rows("10:15").EntireRow.Hidden = True
ActiveSheet.PageSetup.PrintArea = "$A$1:$H$9"
End If
End Sub
Jan 12 2020 03:05 PM
Thank you very much for the code to set the print area. It works great and I really appreciate your help.
Feb 05 2020 02:31 PM
Jul 13 2020 11:12 AM
Jul 13 2020 11:13 AM
Nov 02 2020 02:04 PM
I am looking to create a worksheet where a user is presented with different options (more than 2 options) from a dropdown and based on their responses (i.e if user chooses any one of 2 options - eg. choice A or choice B), a new question appears on the Excelsheet in a row below the first question, otherwise they don't.
And then based on the users' response to this second question (posed as a dropdown choice), a third question appears or does not appear.
As is obvious, I am seeking to have several nested questions within the Excelsheet where each subsequent question shows up as a Dropdown based on the user's response from a previous question (also a dropdown).
I am able to create dropdowns for each question and create one of the questions to appear based on a prior question, but am having trouble going any further.
Any help would be much appreciated. Thank you!
Nov 02 2020 02:17 PM
Perhaps you mean nested drop-down list. See, for example, https://www.youtube.com/watch?v=7mo4COng7Sg
Nov 02 2020 02:38 PM
Nov 14 2020 09:17 AM
@RishiSumit This is exactly what I am trying to do. Did you find a resolution? I originally created this structure:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Select Hyper-V
If Range("B26") = "Hyper-V" Then
Rows("28:29").EntireRow.Hidden = False
Else
Rows("27").EntireRow.Hidden = True
End If
'Select VMware
If Range("B26") = "VMware" Then
Rows("27").EntireRow.Hidden = False
Else
Rows("28:29").EntireRow.Hidden = True
End If
End Sub
It worked once or twice then it stopped working. Now what is happening is I can choose VMware from the dropdown and it shows and hides the correct lines. However, when I change the field to Hyper-V, it does not remove the VMware rows and replace them with the Hyper-V rows.
Thanks,
Jerry
Nov 14 2020 12:17 PM
@Jerry_Reddick Try this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Select Case Range("B26")
Case "Hyper-V"
Rows("28:29").EntireRow.Hidden = False
Rows("27").EntireRow.Hidden = True
Case "VMware"
Rows("27").EntireRow.Hidden = False
Rows("28:29").EntireRow.Hidden = True
End Select
End Sub
Nov 15 2020 01:36 PM
Thanks @Riny_van_Eekelen and @Jerry_Reddick for your responses.
Yes, for me, I am able to hide specific rows for my worksheet.
This is the code I am using :
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C14") = "Residential refinance" Then
Rows("18:19").EntireRow.Hidden = False
Else
Rows("18:19").EntireRow.Hidden = True
End If
If Range("C21") = "Medico" Then
Rows("44:45").EntireRow.Hidden = True
Else
Rows("44:45").EntireRow.Hidden = False
End If
If Range("C32") = "Yes" Then
Rows("34:37").EntireRow.Hidden = False
Else
Rows("34:37").EntireRow.Hidden = True
End If
End Sub
__________________________________________
However, until now I wasnt able to hide the rows for each of the two options from Cell C21 dropdown - only one.
When I select the other option, both follow-up questions remain visible (rows 42:43) & rows (44:45). I will now try @Riny_van_Eekelen 's code because it seems like that might allow me to only keep one specific question (either rows 42:43 or rowas44:45) visible depending on the dropdown option selected from cell C21.
Other than that, my code has been running properly so far (key word: So far). Lets see how it behaves once I share the Excelsheet with my end-users. (coz that's usually when it begins to act up usually)
Thank you guys!