+ Reply to Thread
Results 1 to 5 of 5

Help with "If Range" function

  1. 07-21-2023, 11:41 AM #1
    MartinR87
    MartinR87 is offline
    Registered User
    Join Date
    07-21-2023
    Location
    England
    MS-Off Ver
    office 365
    Posts
    3

    Help with "If Range" function

    Hi Everyone,

    Apologies, i'm very new to VBA so sorry if anything i'm saying doesn't make sense. I feel like i am part way there with my issue but struggling getting the second part working.

    I have 2 drop down boxes
    B1 with 2 options - "Pragmatic" or "Full Review"- the idea being if the "Pragmatic" option is selected then specified rows will be hidden - when Full review is selected every row should be unhidden. I have gotten this working how i need using the formula pasted below (for some reason i couldn't get commas inbetween each row working so i've added individual lines)

    however my issue is I have a second drop down box in cell B2. which is a "Yes" or "No" option added - If "No" is selected then i want columns C : E to be hidden - If Yes then they can be kept unhidden.

    the 2 aren't particularly dependant on each other and i'm really struggling how to get the second "If" function working - i.e If B1 drop down is Pragmatic how do i then also get the columns hidden when B2 is "No"

    I feel like i need to "Nest"? 2 If functions with each other - and i quite frankly haven't got a clue - i've tried adding "If Range ("B1") = "Pragmatic" And If Range ("B2")="No" with a column line added but i can't quite seem to get there.

    I've also attached an image of my spreadsheet (apologies but i've had to clear out some of the contents) but hopefully this gives the idea of what im looking for - in this instance i've got the rows hidden but i now want to be able to select the drop down in b2 as no mean that columns C to E will be hidden.

    If anyone can help it will be greatly appreciated
    PHP Code: 
    Private Sub Worksheet_Change(ByVal Target As Range)
        If     Range("B1") = "Pragmatic" Then
            Rows    ("3").EntireRow.Hidden = True
    Rows    ("4").EntireRow.Hidden = True
    Rows    ("5").EntireRow.Hidden = True
    Rows    ("7").EntireRow.Hidden = True
    Rows    ("8").EntireRow.Hidden = True
    Rows    ("9").EntireRow.Hidden = True
    Rows    ("10").EntireRow.Hidden = True
    Rows    ("17").EntireRow.Hidden = True
    Rows    ("18").EntireRow.Hidden = True
    Rows    ("19").EntireRow.Hidden = True
    Rows    ("20").EntireRow.Hidden = True
    Rows    ("21").EntireRow.Hidden = True
    Rows    ("23").EntireRow.Hidden = True
    Rows    ("28").EntireRow.Hidden = True
    Rows    ("29").EntireRow.Hidden = True
    Rows    ("30").EntireRow.Hidden = True
    Rows    ("31").EntireRow.Hidden = True
    Rows    ("32").EntireRow.Hidden = True
    Rows    ("43").EntireRow.Hidden = True
    Rows    ("44").EntireRow.Hidden = True
    Rows    ("48").EntireRow.Hidden = True
    Rows    ("50").EntireRow.Hidden = True
    Rows    ("51").EntireRow.Hidden = True
    Rows    ("53").EntireRow.Hidden = True
    Rows    ("62").EntireRow.Hidden = True

         
                  Else
                Rows("3").EntireRow.Hidden = False
    Rows    ("4").EntireRow.Hidden = False
    Rows    ("5").EntireRow.Hidden = False
    Rows    ("7").EntireRow.Hidden = Fales
    Rows    ("8").EntireRow.Hidden = False
    Rows    ("9").EntireRow.Hidden = False
    Rows    ("10").EntireRow.Hidden = False
    Rows    ("17").EntireRow.Hidden = False
    Rows    ("18").EntireRow.Hidden = False
    Rows    ("19").EntireRow.Hidden = False
    Rows    ("20").EntireRow.Hidden = False
    Rows    ("21").EntireRow.Hidden = False
    Rows    ("23").EntireRow.Hidden = False
    Rows    ("28").EntireRow.Hidden = False
    Rows    ("29").EntireRow.Hidden = False
    Rows    ("30").EntireRow.Hidden = False
    Rows    ("31").EntireRow.Hidden = False
    Rows    ("32").EntireRow.Hidden = False
    Rows    ("43").EntireRow.Hidden = False
    Rows    ("44").EntireRow.Hidden = False
    Rows    ("48").EntireRow.Hidden = False
    Rows    ("50").EntireRow.Hidden = False
    Rows    ("51").EntireRow.Hidden = False
    Rows    ("53").EntireRow.Hidden = False
    Rows    ("62").EntireRow.Hidden = False

    End     If
    End Sub 
    excel 1.jpg
    Reply With QuoteRegister To Reply
  2. 07-21-2023, 11:53 AM #2
    1aaaaaaaaaaaaaaa
    1aaaaaaaaaaaaaaa is offline
    Valued Forum Contributor
    Join Date
    02-06-2014
    Location
    N/A
    MS-Off Ver
    N/A
    Posts
    373

    Re: Help with "If Range" function

    Hi there,

    You can test for multiple conditions using this syntax

    Please Login or Register  to view this content.
    Or
    Please Login or Register  to view this content.
    Or
    Please Login or Register  to view this content.
    Note that the conditionals in parenthesis are evaluated first.

    You can also use If with Else If in the use case that you don't want a "default" block of code.

    Please Login or Register  to view this content.
    Note that there is no "default" else code block that always triggers when the if condition(s) are false using this syntax.
    Last edited by 1aaaaaaaaaaaaaaa; 07-21-2023 at 11:58 AM.
    Reply With QuoteRegister To Reply
  3. 07-21-2023, 01:27 PM #3
    MartinR87
    MartinR87 is offline
    Registered User
    Join Date
    07-21-2023
    Location
    England
    MS-Off Ver
    office 365
    Posts
    3

    Re: Help with "If Range" function

    Thanks for the reply,

    i am trying but it feels like using multiple conditions is meaning that both drop downs need to be dependent on the other

    is there a way for example to add in something like the below that would hopefully work alongside/in addition to the original code/macro?

    If Range("B2") = "No" Then
    Columns("C:E").EntireColumn.Hidden = True
    Else
    Columns("C:E").EntireColumn.Hidden = False


    Apologies if i'm coming across as a bit thick but i'm struggling to get my head around this one.
    Reply With QuoteRegister To Reply
  4. 07-21-2023, 01:41 PM #4
    1aaaaaaaaaaaaaaa
    1aaaaaaaaaaaaaaa is offline
    Valued Forum Contributor
    Join Date
    02-06-2014
    Location
    N/A
    MS-Off Ver
    N/A
    Posts
    373

    Re: Help with "If Range" function

    Yes, rows and columns are hidden independently from each other.

    In your OnChange code, create a conditional statement for your 1st dropdown, and another conditional statement for your 2nd dropdown. They should not be nested in this use case.

    Attach an example workbook for us to work off of if you want more specific help.

    Please Login or Register  to view this content.
    Last edited by 1aaaaaaaaaaaaaaa; 07-21-2023 at 01:45 PM.
    Reply With QuoteRegister To Reply
  5. 07-21-2023, 02:42 PM #5
    MartinR87
    MartinR87 is offline
    Registered User
    Join Date
    07-21-2023
    Location
    England
    MS-Off Ver
    office 365
    Posts
    3

    Re: Help with "If Range" function

    Thanks for that, So i've tried the following code, The function for column B1 and hiding the rows is working but nothing is happening when i change the option in the dropdown in B2? 

    Please Login or Register  to view this content.


    So , Thought best to edit.... I've got the code in now as per below and this appears to be working now. Not sure what i'd done wrong but this is all working now. Thanks for your help!

    Please Login or Register  to view this content.
    Last edited by MartinR87; 07-21-2023 at 03:07 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find MAX value from a range of time intervals displayed as "days", "hrs", "mins"
    By Liam5 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 06-05-2020, 02:08 AM
  2. [SOLVED] Column X-Ref list - Sheet1 Col A "pages", Col B:FL "Req" to Sheet2 ColA "req", ColB "page"
    By excel-card-pulled in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-07-2017, 09:30 AM
  3. [SOLVED] If Not ActiveSheet.Range("A1").Value Like "apple" Then MsgBox "Error"
    By HerryMarkowitz in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-18-2014, 02:16 PM
  4. How to check if a range of cells contains the word "unresponsive" and return "yes" or "no"
    By benitopjuarez in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  5. if Range("A1") = "Mike" Then Range("A1") = "michael" Then Exit Sub
    By a8015945 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-30-2013, 10:08 AM
  6. [SOLVED] Variable "sheet-name" and "range-name" wanted in INDEX/MATCH-function
    By Fiebuls in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-01-2012, 04:09 PM
  7. use variable in Workbooks("book1").Worksheets("sheet1").Range("a1"
    By Luc in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2005, 04:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1