Results 1 to 4 of 4
  1. #1
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40

    Clear Cell Range Based on Text in Another Cell

    I have a sheet where I'm trying to clear a range based on value in a cell with a listbox. I have been using the code below which works if the listbox cell (B2) is changed.

    Private Sub Worksheet_Change(ByVal Target As Range)



    If Not Intersect(Target, Range("B2")) Is Nothing Then
    Range("F2:J2").ClearContents
    End If

    End Sub

    The problem with this is I want the range to differ if the cell equals specific text instead of any change at all. Any help is much appreciated.

    Example:
    If B2 is changed to "Regular" clear range F2:J2
    If B2 is changed to "Regular (extra)" clear range G2:J2

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If I get it, and there is only 1 cell address to be changed (B2) and not too many possibilities (for the contents of B2) to examine, then use a Select Case block? If I remember this event, it fires when any cell is changed on the sheet, so just examine the contents of B2.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Not Intersect(Target, Range("B2")) Is Nothing Then
      Select Case Range("B2")
       Case "Regular"
        Range("F2:J2").ClearContents
      Case "Regular (extra)" 
        Range("G2:J2").ClearContents
    '...and so on
      End Select
    End If
    
    End Sub
    If you want to vary the range based on some input, declare a range variable and assign the address to it, then use the variable in the Select statements.
    Not sure if you need to do anything further to examine the contents of B2 as in Select Case Range("B2").something or copy and assign to a variable and use the variable in the Select statement. My Excel vba knowledge is not real deep.
    Last edited by Micron; 08-20-2017 at 07:53 PM. Reason: added info
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Oxygen Potassium is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    40
    Wow I can't believe I missed that simple solution. Thank you very much Micron that solved my problem.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome! I wasn't sure that the Range("B2") reference would translate to what's contained in the cell at that address. Glad to have helped, and thanks for marking your thread as solved. Do you frequently post here for Excel questions? Might not always result in a solution. Then again, maybe the regulars here are Excel wizards too...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-22-2016, 04:36 AM
  2. Replies: 1
    Last Post: 07-12-2013, 01:48 PM
  3. Replies: 4
    Last Post: 06-18-2013, 08:50 PM
  4. Replies: 12
    Last Post: 01-15-2013, 02:35 PM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums