Hide specific rows if range contains certain values The 2019 Stack Overflow Developer Survey Results Are InMy Copying code doesn't copy entire row valueExcel VBA Insert/delete Row in SheetExcel vba: Finding pair of boolean values in range row by rowHiding title rows of empty data chartsVBA-Excel. How can I handle hide/unhide rows based on Private Sub Worksheet_Change(ByVal Target As Range)?Loop to check when workbook closeVBA Modify Range when value is observedAuto-Increment if Column Already Contains a Certain ValueColour a cell if a certain number is enteredHow to stop certain rows from printing if one single cell is blank
Worn-tile Scrabble
Is three citations per paragraph excessive for undergraduate research paper?
What tool would a Roman-age civilization have for the breaking of silver and other metals into dust?
What is the most effective way of iterating a std::vector and why?
Shouldn't "much" here be used instead of "more"?
A poker game description that does not feel gimmicky
What are the motivations for publishing new editions of an existing textbook, beyond new discoveries in a field?
Why do UK politicians seemingly ignore opinion polls on Brexit?
Is flight data recorder erased after every flight?
Button changing it's text & action. Good or terrible?
What do the Banks children have against barley water?
Falsification in Math vs Science
Did Scotland spend $250,000 for the slogan "Welcome to Scotland"?
Is a "Democratic" Oligarchy-Style System Possible?
How to notate time signature switching consistently every measure
What did it mean to "align" a radio?
What does "fetching by region is not available for SAM files" means?
Why not take a picture of a closer black hole?
How to save as into a customized destination on macOS?
Why hard-Brexiteers don't insist on a hard border to prevent illegal immigration after Brexit?
How to answer pointed "are you quitting" questioning when I don't want them to suspect
Is this app Icon Browser Safe/Legit?
How can I autofill dates in Excel excluding Sunday?
Why didn't the Event Horizon Telescope team mention Sagittarius A*?
Hide specific rows if range contains certain values
The 2019 Stack Overflow Developer Survey Results Are InMy Copying code doesn't copy entire row valueExcel VBA Insert/delete Row in SheetExcel vba: Finding pair of boolean values in range row by rowHiding title rows of empty data chartsVBA-Excel. How can I handle hide/unhide rows based on Private Sub Worksheet_Change(ByVal Target As Range)?Loop to check when workbook closeVBA Modify Range when value is observedAuto-Increment if Column Already Contains a Certain ValueColour a cell if a certain number is enteredHow to stop certain rows from printing if one single cell is blank
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
0
I would like to have some help with automatizing my data sheet.
In the range of E7:V7 (hereinafter, r) I have same drop down lists, each of them has four different values ("-"; "open"; "close"; "both").
When r contains only "-", I would like to have rows 21:50 hidden.
"open"shows rows21:30"close"shows rows31:50"both"shows rows21:50
For example:
- if E7= "-", F7="open", then rows 21:30 are shown and 31:50 hidden.
- if E7="-", F7="both", then all rows are shown.
I hope it was clear enough.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E7").Value = "-" Then
Rows("21:50").EntireRow.Hidden = True
ElseIf Range("E7").Value = "open" Then
Rows("21:30").EntireRow.Hidden = False
Rows("31:50").EntireRow.Hidden = True
ElseIf Range("E7").Value = "close" Then
Rows("31:50").EntireRow.Hidden = False
Rows("21:30").EntireRow.Hidden = True
ElseIf Range("E7").Value = "both" Then
Rows("21:50").EntireRow.Hidden = False
End If
End Sub
This code works only for one criteria, but I hope it helps to clarify the situation.
excel vba
edited Mar 8 at 12:36
Pᴇʜ
25.2k63052
asked Mar 8 at 9:39
VBwArriorVBwArrior
133
add a comment |
0
I would like to have some help with automatizing my data sheet.
In the range of E7:V7 (hereinafter, r) I have same drop down lists, each of them has four different values ("-"; "open"; "close"; "both").
When r contains only "-", I would like to have rows 21:50 hidden.
"open"shows rows21:30"close"shows rows31:50"both"shows rows21:50
For example:
- if E7= "-", F7="open", then rows 21:30 are shown and 31:50 hidden.
- if E7="-", F7="both", then all rows are shown.
I hope it was clear enough.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E7").Value = "-" Then
Rows("21:50").EntireRow.Hidden = True
ElseIf Range("E7").Value = "open" Then
Rows("21:30").EntireRow.Hidden = False
Rows("31:50").EntireRow.Hidden = True
ElseIf Range("E7").Value = "close" Then
Rows("31:50").EntireRow.Hidden = False
Rows("21:30").EntireRow.Hidden = True
ElseIf Range("E7").Value = "both" Then
Rows("21:50").EntireRow.Hidden = False
End If
End Sub
This code works only for one criteria, but I hope it helps to clarify the situation.
excel vba
edited Mar 8 at 12:36
Pᴇʜ
25.2k63052
asked Mar 8 at 9:39
VBwArriorVBwArrior
133
2
Can you show your code so far?
– Nathan_Sav
Mar 8 at 9:45
If you did not start coding yet, start your research at the Worksheet.Change Event. Also the Application.Intersect method might be useful, as well as the Select Case statement. • Give it a try.
– Pᴇʜ
Mar 8 at 9:57
So the snippet you've posted does what you want it to do for column E, and you want to do the same for F:V?
– jsheeran
Mar 8 at 11:07
add a comment |
0
0
0
I would like to have some help with automatizing my data sheet.
In the range of E7:V7 (hereinafter, r) I have same drop down lists, each of them has four different values ("-"; "open"; "close"; "both").
When r contains only "-", I would like to have rows 21:50 hidden.
"open"shows rows21:30"close"shows rows31:50"both"shows rows21:50
For example:
- if E7= "-", F7="open", then rows 21:30 are shown and 31:50 hidden.
- if E7="-", F7="both", then all rows are shown.
I hope it was clear enough.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E7").Value = "-" Then
Rows("21:50").EntireRow.Hidden = True
ElseIf Range("E7").Value = "open" Then
Rows("21:30").EntireRow.Hidden = False
Rows("31:50").EntireRow.Hidden = True
ElseIf Range("E7").Value = "close" Then
Rows("31:50").EntireRow.Hidden = False
Rows("21:30").EntireRow.Hidden = True
ElseIf Range("E7").Value = "both" Then
Rows("21:50").EntireRow.Hidden = False
End If
End Sub
This code works only for one criteria, but I hope it helps to clarify the situation.
excel vba
edited Mar 8 at 12:36
Pᴇʜ
25.2k63052
asked Mar 8 at 9:39
VBwArriorVBwArrior
133
I would like to have some help with automatizing my data sheet.
In the range of E7:V7 (hereinafter, r) I have same drop down lists, each of them has four different values ("-"; "open"; "close"; "both").
When r contains only "-", I would like to have rows 21:50 hidden.
"open"shows rows21:30"close"shows rows31:50"both"shows rows21:50
For example:
- if E7= "-", F7="open", then rows 21:30 are shown and 31:50 hidden.
- if E7="-", F7="both", then all rows are shown.
I hope it was clear enough.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("E7").Value = "-" Then
Rows("21:50").EntireRow.Hidden = True
ElseIf Range("E7").Value = "open" Then
Rows("21:30").EntireRow.Hidden = False
Rows("31:50").EntireRow.Hidden = True
ElseIf Range("E7").Value = "close" Then
Rows("31:50").EntireRow.Hidden = False
Rows("21:30").EntireRow.Hidden = True
ElseIf Range("E7").Value = "both" Then
Rows("21:50").EntireRow.Hidden = False
End If
End Sub
This code works only for one criteria, but I hope it helps to clarify the situation.
excel vba
excel vba
edited Mar 8 at 12:36
Pᴇʜ
25.2k63052
asked Mar 8 at 9:39
VBwArriorVBwArrior
133
edited Mar 8 at 12:36
Pᴇʜ
25.2k63052
asked Mar 8 at 9:39
VBwArriorVBwArrior
133
edited Mar 8 at 12:36
Pᴇʜ
25.2k63052
edited Mar 8 at 12:36
Pᴇʜ
25.2k63052
edited Mar 8 at 12:36
Pᴇʜ
25.2k63052
25.2k63052
asked Mar 8 at 9:39
VBwArriorVBwArrior
133
asked Mar 8 at 9:39
VBwArriorVBwArrior
133
asked Mar 8 at 9:39
VBwArriorVBwArrior
133
133
2
Can you show your code so far?
– Nathan_Sav
Mar 8 at 9:45
If you did not start coding yet, start your research at the Worksheet.Change Event. Also the Application.Intersect method might be useful, as well as the Select Case statement. • Give it a try.
– Pᴇʜ
Mar 8 at 9:57
So the snippet you've posted does what you want it to do for column E, and you want to do the same for F:V?
– jsheeran
Mar 8 at 11:07
add a comment |
2
Can you show your code so far?
– Nathan_Sav
Mar 8 at 9:45
If you did not start coding yet, start your research at the Worksheet.Change Event. Also the Application.Intersect method might be useful, as well as the Select Case statement. • Give it a try.
– Pᴇʜ
Mar 8 at 9:57
So the snippet you've posted does what you want it to do for column E, and you want to do the same for F:V?
– jsheeran
Mar 8 at 11:07
2
2
Can you show your code so far?
– Nathan_Sav
Mar 8 at 9:45
Can you show your code so far?
– Nathan_Sav
Mar 8 at 9:45
If you did not start coding yet, start your research at the Worksheet.Change Event. Also the Application.Intersect method might be useful, as well as the Select Case statement. • Give it a try.
– Pᴇʜ
Mar 8 at 9:57
If you did not start coding yet, start your research at the Worksheet.Change Event. Also the Application.Intersect method might be useful, as well as the Select Case statement. • Give it a try.
– Pᴇʜ
Mar 8 at 9:57
So the snippet you've posted does what you want it to do for column E, and you want to do the same for F:V?
– jsheeran
Mar 8 at 11:07
So the snippet you've posted does what you want it to do for column E, and you want to do the same for F:V?
– jsheeran
Mar 8 at 11:07
add a comment |
1 Answer
1
active
oldest
votes
0
As suggested in my comment: Use the Application.Intersect method, as well as the Select Case statement.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AffectedCells As Range
Set AffectedCells = Intersect(Target, Target.Parent.Range("E7:V7"))
If Not AffectedCells Is Nothing Then
Dim Cell As Range
For Each Cell In AffectedCells
Select Case Cell.Value
Case "-"
Target.Parent.Rows("21:50").Hidden = True
Case "open"
Target.Parent.Rows("21:30").Hidden = False
Target.Parent.Rows("31:50").Hidden = True
Case "close"
Target.Parent.Rows("31:50").Hidden = False
Target.Parent.Rows("21:30").Hidden = True
Case "both"
Target.Parent.Rows("21:50").Hidden = False
End Select
Next Cell
End If
End Sub
answered Mar 8 at 11:15
PᴇʜPᴇʜ
25.2k63052
Thank you for your response! It is almost the result I need, but for example if I select E7="open" and after that F7="closed", it hides "open" rows. Is it possible to keep both selections unhidden in this case.
– VBwArrior
Mar 8 at 12:40
Or for example one selection in range is "both" the the rows will stay shown, no matter what further selections will be. Thanks!
– VBwArrior
Mar 8 at 12:42
Of course you have more than one switch for the same logic. The last changed switch changes what to show/hide. If you want something else you need to re-think your logic. • Actually there should be only one cell/switch to show/hide the rows. Anything else doesn't make much sense to me. • Try to remove the 2.Hidden = Truelines fromopenandclose.
– Pᴇʜ
Mar 8 at 12:50
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55060429%2fhide-specific-rows-if-range-contains-certain-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
As suggested in my comment: Use the Application.Intersect method, as well as the Select Case statement.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AffectedCells As Range
Set AffectedCells = Intersect(Target, Target.Parent.Range("E7:V7"))
If Not AffectedCells Is Nothing Then
Dim Cell As Range
For Each Cell In AffectedCells
Select Case Cell.Value
Case "-"
Target.Parent.Rows("21:50").Hidden = True
Case "open"
Target.Parent.Rows("21:30").Hidden = False
Target.Parent.Rows("31:50").Hidden = True
Case "close"
Target.Parent.Rows("31:50").Hidden = False
Target.Parent.Rows("21:30").Hidden = True
Case "both"
Target.Parent.Rows("21:50").Hidden = False
End Select
Next Cell
End If
End Sub
answered Mar 8 at 11:15
PᴇʜPᴇʜ
25.2k63052
Thank you for your response! It is almost the result I need, but for example if I select E7="open" and after that F7="closed", it hides "open" rows. Is it possible to keep both selections unhidden in this case.
– VBwArrior
Mar 8 at 12:40
Or for example one selection in range is "both" the the rows will stay shown, no matter what further selections will be. Thanks!
– VBwArrior
Mar 8 at 12:42
Of course you have more than one switch for the same logic. The last changed switch changes what to show/hide. If you want something else you need to re-think your logic. • Actually there should be only one cell/switch to show/hide the rows. Anything else doesn't make much sense to me. • Try to remove the 2.Hidden = Truelines fromopenandclose.
– Pᴇʜ
Mar 8 at 12:50
add a comment |
0
As suggested in my comment: Use the Application.Intersect method, as well as the Select Case statement.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AffectedCells As Range
Set AffectedCells = Intersect(Target, Target.Parent.Range("E7:V7"))
If Not AffectedCells Is Nothing Then
Dim Cell As Range
For Each Cell In AffectedCells
Select Case Cell.Value
Case "-"
Target.Parent.Rows("21:50").Hidden = True
Case "open"
Target.Parent.Rows("21:30").Hidden = False
Target.Parent.Rows("31:50").Hidden = True
Case "close"
Target.Parent.Rows("31:50").Hidden = False
Target.Parent.Rows("21:30").Hidden = True
Case "both"
Target.Parent.Rows("21:50").Hidden = False
End Select
Next Cell
End If
End Sub
answered Mar 8 at 11:15
PᴇʜPᴇʜ
25.2k63052
Thank you for your response! It is almost the result I need, but for example if I select E7="open" and after that F7="closed", it hides "open" rows. Is it possible to keep both selections unhidden in this case.
– VBwArrior
Mar 8 at 12:40
Or for example one selection in range is "both" the the rows will stay shown, no matter what further selections will be. Thanks!
– VBwArrior
Mar 8 at 12:42
Of course you have more than one switch for the same logic. The last changed switch changes what to show/hide. If you want something else you need to re-think your logic. • Actually there should be only one cell/switch to show/hide the rows. Anything else doesn't make much sense to me. • Try to remove the 2.Hidden = Truelines fromopenandclose.
– Pᴇʜ
Mar 8 at 12:50
add a comment |
0
0
0
As suggested in my comment: Use the Application.Intersect method, as well as the Select Case statement.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AffectedCells As Range
Set AffectedCells = Intersect(Target, Target.Parent.Range("E7:V7"))
If Not AffectedCells Is Nothing Then
Dim Cell As Range
For Each Cell In AffectedCells
Select Case Cell.Value
Case "-"
Target.Parent.Rows("21:50").Hidden = True
Case "open"
Target.Parent.Rows("21:30").Hidden = False
Target.Parent.Rows("31:50").Hidden = True
Case "close"
Target.Parent.Rows("31:50").Hidden = False
Target.Parent.Rows("21:30").Hidden = True
Case "both"
Target.Parent.Rows("21:50").Hidden = False
End Select
Next Cell
End If
End Sub
answered Mar 8 at 11:15
PᴇʜPᴇʜ
25.2k63052
As suggested in my comment: Use the Application.Intersect method, as well as the Select Case statement.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AffectedCells As Range
Set AffectedCells = Intersect(Target, Target.Parent.Range("E7:V7"))
If Not AffectedCells Is Nothing Then
Dim Cell As Range
For Each Cell In AffectedCells
Select Case Cell.Value
Case "-"
Target.Parent.Rows("21:50").Hidden = True
Case "open"
Target.Parent.Rows("21:30").Hidden = False
Target.Parent.Rows("31:50").Hidden = True
Case "close"
Target.Parent.Rows("31:50").Hidden = False
Target.Parent.Rows("21:30").Hidden = True
Case "both"
Target.Parent.Rows("21:50").Hidden = False
End Select
Next Cell
End If
End Sub
answered Mar 8 at 11:15
PᴇʜPᴇʜ
25.2k63052
answered Mar 8 at 11:15
PᴇʜPᴇʜ
25.2k63052
answered Mar 8 at 11:15
PᴇʜPᴇʜ
25.2k63052
answered Mar 8 at 11:15
PᴇʜPᴇʜ
25.2k63052
25.2k63052
Thank you for your response! It is almost the result I need, but for example if I select E7="open" and after that F7="closed", it hides "open" rows. Is it possible to keep both selections unhidden in this case.
– VBwArrior
Mar 8 at 12:40
Or for example one selection in range is "both" the the rows will stay shown, no matter what further selections will be. Thanks!
– VBwArrior
Mar 8 at 12:42
Of course you have more than one switch for the same logic. The last changed switch changes what to show/hide. If you want something else you need to re-think your logic. • Actually there should be only one cell/switch to show/hide the rows. Anything else doesn't make much sense to me. • Try to remove the 2.Hidden = Truelines fromopenandclose.
– Pᴇʜ
Mar 8 at 12:50
add a comment |
Thank you for your response! It is almost the result I need, but for example if I select E7="open" and after that F7="closed", it hides "open" rows. Is it possible to keep both selections unhidden in this case.
– VBwArrior
Mar 8 at 12:40
Or for example one selection in range is "both" the the rows will stay shown, no matter what further selections will be. Thanks!
– VBwArrior
Mar 8 at 12:42
Of course you have more than one switch for the same logic. The last changed switch changes what to show/hide. If you want something else you need to re-think your logic. • Actually there should be only one cell/switch to show/hide the rows. Anything else doesn't make much sense to me. • Try to remove the 2.Hidden = Truelines fromopenandclose.
– Pᴇʜ
Mar 8 at 12:50
Thank you for your response! It is almost the result I need, but for example if I select E7="open" and after that F7="closed", it hides "open" rows. Is it possible to keep both selections unhidden in this case.
– VBwArrior
Mar 8 at 12:40
Thank you for your response! It is almost the result I need, but for example if I select E7="open" and after that F7="closed", it hides "open" rows. Is it possible to keep both selections unhidden in this case.
– VBwArrior
Mar 8 at 12:40
Or for example one selection in range is "both" the the rows will stay shown, no matter what further selections will be. Thanks!
– VBwArrior
Mar 8 at 12:42
Or for example one selection in range is "both" the the rows will stay shown, no matter what further selections will be. Thanks!
– VBwArrior
Mar 8 at 12:42
Of course you have more than one switch for the same logic. The last changed switch changes what to show/hide. If you want something else you need to re-think your logic. • Actually there should be only one cell/switch to show/hide the rows. Anything else doesn't make much sense to me. • Try to remove the 2
.Hidden = True lines from open and close.– Pᴇʜ
Mar 8 at 12:50
Of course you have more than one switch for the same logic. The last changed switch changes what to show/hide. If you want something else you need to re-think your logic. • Actually there should be only one cell/switch to show/hide the rows. Anything else doesn't make much sense to me. • Try to remove the 2
.Hidden = True lines from open and close.– Pᴇʜ
Mar 8 at 12:50
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55060429%2fhide-specific-rows-if-range-contains-certain-values%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
2
Can you show your code so far?
– Nathan_Sav
Mar 8 at 9:45
If you did not start coding yet, start your research at the Worksheet.Change Event. Also the Application.Intersect method might be useful, as well as the Select Case statement. • Give it a try.
– Pᴇʜ
Mar 8 at 9:57
So the snippet you've posted does what you want it to do for column E, and you want to do the same for F:V?
– jsheeran
Mar 8 at 11:07