Ggtcf

How to be diplomatic in refusing to write code that breaches the privacy of our users

How was Earth single-handedly capable of creating 3 of the 4 gods of chaos?

How does residential electricity work?

The baby cries all morning

How does a character multiclassing into warlock get a focus?

Can somebody explain Brexit in a few child-proof sentences?

What's the purpose of "true" in bash "if sudo true; then"

Is it correct to write "is not focus on"?

Failed to fetch jessie backports repository

What would happen if the UK refused to take part in EU Parliamentary elections?

Efficiently merge handle parallel feature branches in SFDX

How can I replace every global instance of "x[2]" with "x_2"

How do I rename a LINUX host without needing to reboot for the rename to take effect?

Implement the Thanos sorting algorithm

Is the destination of a commercial flight important for the pilot?

Bash method for viewing beginning and end of file

How could Frankenstein get the parts for his _second_ creature?

What is the oldest known work of fiction?

Lay out the Carpet

Everything Bob says is false. How does he get people to trust him?

Where in the Bible does the greeting ("Dominus Vobiscum") used at Mass come from?

Why are on-board computers allowed to change controls without notifying the pilots?

How do I keep an essay about "feeling flat" from feeling flat?

How can I use the arrow sign in my bash prompt?

Hide specific rows if range contains certain values

My 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 ValueUse excel-VBA to colour a cell if a certain number is placedHow to stop certain rows from printing if one single cell is blank

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 rows 21:30
  


• 
  "close" shows rows 31:50
  


• 
  "both" shows rows 21: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

share|improve this question

edited Mar 8 at 12:36

Pᴇʜ

24.7k63052

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

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 rows 21:30
  


• 
  "close" shows rows 31:50
  


• 
  "both" shows rows 21: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

share|improve this question

edited Mar 8 at 12:36

Pᴇʜ

24.7k63052

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 | 

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 rows 21:30
  


• 
  "close" shows rows 31:50
  


• 
  "both" shows rows 21: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

share|improve this question

edited Mar 8 at 12:36

Pᴇʜ

24.7k63052

asked Mar 8 at 9:39

VBwArriorVBwArrior

133

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

share|improve this question

edited Mar 8 at 12:36

Pᴇʜ

24.7k63052

asked Mar 8 at 9:39

VBwArriorVBwArrior

133

share|improve this question

edited Mar 8 at 12:36

Pᴇʜ

24.7k63052

asked Mar 8 at 9:39

VBwArriorVBwArrior

133

edited Mar 8 at 12:36

Pᴇʜ

24.7k63052

edited Mar 8 at 12:36

Pᴇʜ

24.7k63052

asked Mar 8 at 9:39

VBwArriorVBwArrior

133

asked Mar 8 at 9:39

VBwArriorVBwArrior

133

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

share|improve this answer

answered Mar 8 at 11:15

PᴇʜPᴇʜ

24.7k63052

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 = True lines from open and close.
  
  – 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

Name

Email

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

Name

Email

Required, but never shown

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

share|improve this answer

answered Mar 8 at 11:15

PᴇʜPᴇʜ

24.7k63052

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 = True lines from open and close.
  
  – 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

share|improve this answer

answered Mar 8 at 11:15

PᴇʜPᴇʜ

24.7k63052

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 = True lines from open and close.
  
  – Pᴇʜ
  Mar 8 at 12:50
  
  

  
  
  
  

add a comment | 

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

share|improve this answer

answered Mar 8 at 11:15

PᴇʜPᴇʜ

24.7k63052

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

share|improve this answer

answered Mar 8 at 11:15

PᴇʜPᴇʜ

24.7k63052

answered Mar 8 at 11:15

PᴇʜPᴇʜ

24.7k63052

answered Mar 8 at 11:15

PᴇʜPᴇʜ

24.7k63052