+ Reply to Thread
Results 1 to 6 of 6

VBA clear 2 cells when 1 cell has text

  1. #1
    Registered User
    Join Date
    09-30-2019
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    2

    VBA clear 2 cells when 1 cell has text

    Hi All,

    I can't figure this out. I have 2 ranges of cells that either contain an "X" or are empty "".

    I made the script for the X to be on or off.

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
    Cancel As Boolean)
    With Application

    .Calculation = xlCalculationManual
    .ScreenUpdating = False
    .DisplayAlerts = False

    End With

    If Not Intersect(Target, Range("D31:F135")) Is Nothing Then
    If Len(Trim(Target)) = 0 Then
    Target.Value = "X"
    Cancel = True
    ElseIf UCase(Trim(Target)) = "X" Then
    Target.ClearContents
    Cancel = True
    End If
    End If

    The only way i can clear the 2 other cells in columns D,E or F is:

    If Target.Address = Range("D31").Address Then
    Range("E31:F31").Value = ""
    End If
    If Target.Address = Range("E31").Address Then
    Range("D31,F31").Value = ""
    End If
    If Target.Address = Range("F31").Address Then
    Range("D31:E31").Value = ""
    End If

    I have 105 rows in range column D,E and F and another 105 rows in columns I,J and K, all in the same worksheet.

    I get an error 28 out of stack because the script is too big (820 lines eek . There must be an easy way to solve this problem without using checkboxes?
    The code has to activate on start so i have to put it in the worksheet.

    Any suggestions are welcome

    Thanks!

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: VBA clear 2 cells when 1 cell has text

    Hi and welcome to the board.
    Is this what you want
    Please Login or Register  to view this content.
    Also please edit your post to include code tags, the # icon in the reply/edit window.

  3. #3
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: VBA clear 2 cells when 1 cell has text

    If I understand your question/problem correctly, you should be able to replace all the code you have now (all 820 lines) with this...
    Please Login or Register  to view this content.
    NOTE: You might want to test this code out on a new workbook first just in case I am wrong (I would hate for you to delete all of that code you typed and find that my code does not do what you want).
    Last edited by Rick Rothstein; 10-01-2019 at 11:47 AM.

  4. #4
    Registered User
    Join Date
    09-30-2019
    Location
    Netherlands
    MS-Off Ver
    2010
    Posts
    2

    Re: VBA clear 2 cells when 1 cell has text

    Hi Fluff and Rick,

    Thank you for your suggestions. It was not quite what I was looking for but I solved it myself with the ElseIf line.

    Please Login or Register  to view this content.
    Thank you for your help

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: VBA clear 2 cells when 1 cell has text

    Glad you sorted it & thanks for the feedback

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: VBA clear 2 cells when 1 cell has text

    You may have grabbed my code before I modified it to handle both column ranges. See the code that is currently posted in Message #3... it does not require the ElseIf block and I am pretty sure it will work the same as the code you have now posted (note in particular the argument to the Range object).

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Clear Cells that do not contain certain text
    By Howardc1001 in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-13-2019, 08:38 PM
  2. VBA MAcro to clear contents of specific cells if a specific cell contains text
    By muzzareilly in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-28-2017, 12:47 PM
  3. VBA to Clear 1 Cell and Clear other cells Formula only
    By oneblondebrow in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-17-2017, 12:56 PM
  4. [SOLVED] Clear cells if adjacent cell contains certain text from a direct link
    By Groovicles in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 09-01-2016, 12:23 PM
  5. [SOLVED] Clear cells that do not contain specific text which may be at the beginning or end of cell
    By Sianee in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2015, 02:53 PM
  6. [SOLVED] VBA help needed to find text string, clear cells then add new cell value
    By krjoshi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-13-2014, 11:29 AM
  7. Clear contents of cells that do not contain specific text, sort cells that do
    By feckless.lout in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-27-2010, 01:41 AM

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