Results 1 to 8 of 8

Case Range Help

Threaded View

  1. #1
    Registered User
    Join Date
    06-13-2018
    Location
    Glasgow
    MS-Off Ver
    2013
    Posts
    33

    Angry Case Range Help

    I have a VBA function set up whereby certain cells are displayed after ALL dropdowns are selected from 3 cells (D5,D6(Misc 1 or Misc 2) and D7)

    I am looking to have 3 separate cases that return certain outcomes. For example, If D7 shows "CASE 1" this will only shows rows 15 & 19 (Row 17 will be hidden). If D7 shows "CASE 2" this will show rows 15 & 17 (19 will be hidden). Finally, if D7 shows "CASE 3" then this will show row 19 only (15 & 17 will be hidden).

    PS - D6 only has 2 options "MISC 1" and "MISC 2". If "MISC 1" is selected, row 26 will show. If "MISC 2" is selected row 26 will be hidden.

    I am also looking to add more options in i.e. "CASE 4,5,6,etc" that show and hide other rows but I'll tackle that another day.

    I have this VBA but it doesn't work for CASE 1. Could someone offer their advice? PPS - I am new to VBA and it pickles my head at times.

    Thanks.

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    If Range("D5").Value <> "" And Range("D6").Value <> "" And Range("D7").Value <> "" Then
    If Target.Address = "$D$5" Or Target.Address = "$D$6" Or Target.Address = "$D$7" Then
        If Range("D6").Value = "Misc" Then
            Rows("25:25").EntireRow.Hidden = False
        Else
            Rows("25:25").EntireRow.Hidden = True
        End If
        
    Select Case Range("D5").Value
        Case "ABC", "DEF"
        If Range("D7").Value = "CASE1" Then
            Rows("19:19").EntireRow.Hidden = False
            Else
            Rows("19:19").EntireRow.Hidden = True
        End If
        If Range("D7").Value = "CASE2" Then
            Rows("15:15").EntireRow.Hidden = True
            Else
            Rows("15:15").EntireRow.Hidden = False
        End If
            Rows("17:17").EntireRow.Hidden = False
        If Range("D7").Value = "CASE 2" Then
            Rows("17:17").EntireRow.Hidden = True
            Else
            Rows("17:17").EntireRow.Hidden = False
        End If
            Rows("17:17").EntireRow.Hidden = False
       End Select
    End If
    End If
    Application.ScreenUpdating = True
    End Sub
    Last edited by MonTheEck; 09-14-2018 at 04:16 AM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Case or If statement to add value to range
    By rob_h in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 05-05-2017, 06:27 AM
  2. Select case on range name
    By mcinnes01 in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 01-10-2011, 11:26 AM
  3. How to add range to Case Statement
    By Launchnet in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-20-2010, 01:50 PM
  4. Select Case in a Range
    By prairied0gg in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-12-2010, 03:30 PM
  5. Select case using a range
    By sigfreid in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-31-2009, 09:17 AM
  6. how to make this range in this case ?
    By vumian in forum Excel General
    Replies: 6
    Last Post: 08-17-2006, 11:15 AM
  7. case select and a range
    By JT in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-27-2005, 07:06 PM

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1