Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,546
    Thanks
    138
    Thanked 11 Times in 11 Posts

    Clearing Data in same row based on certain Criteria

    I have macro to clear data in the same row In Col Q where a % has been entered in Col O. The same must apply if a value has been entered in Col Q, then the % in Col O in the same row as the value entered in Col Q must be cleared

    For eg If I enter a % in O2 and then decide rather to input a value in Q2, then O2 must be automatically cleared

    It would be appreciated if someone can kindly assist me


    Code:
     Private Sub Worksheet_Change(ByVal Target As Range)
     Application.EnableEvents = False
     If Target.Column = 15 Then
     Cells(Target.Row, 17).ClearContents
     ElseIf Target.Column = 17 Then
     Cells(Target.Row, 15).ClearContents
     End If
     Application.EnableEvents = True
     End Sub

  2. #2
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    3,386
    Thanks
    164
    Thanked 633 Times in 601 Posts
    Hi Howard

    ..what about this..
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    zRow=Target.Row
    zCol=Target.Column
    
    Application.EnableEvents = False
    If zCol= [o1].column Then
    Cells(zRow, "Q").ClearContents
    end if
    
    If zCol= [q1].column Then
    Cells(zRow, "O").ClearContents
    End If
    
    Application.EnableEvents = True
    End Sub
    ..you need to put the code into the code sheet i.e. right-click on the tab sheet name, select 'View code', and then paste it there.

    zeddy
    Deep Sea Pearl Diver
    Last edited by zeddy; 2015-07-03 at 13:27.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,894
    Thanks
    420
    Thanked 1,585 Times in 1,434 Posts
    zeddy
    Deep Sea Pearl Diver

    Now I know where all those Pearls of Excel Wisdom come from...
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,919
    Thanks
    152
    Thanked 746 Times in 678 Posts
    If I understand you correctly, here is Zeddy's code revised to remove the value in column Q if in the Corresponding row col O is a "%". Conversely, the "%" is removed in col O when a value is placed in Q.

    HTH,
    Maud

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    Row = Target.Row
    Col = Target.Column
    
    Application.EnableEvents = False
    If Col = 15 And Target = "%" Then
    Cells(Row, "Q").ClearContents
    End If
    
    If Col = 17 And Target.Offset(0, -2) = "%" Then
    Cells(Row, "O").ClearContents
    End If
    
    Application.EnableEvents = True
    End Sub

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,894
    Thanks
    420
    Thanked 1,585 Times in 1,434 Posts
    Howard,

    Or if you just want toggle if one column has an entry clear the other column in the same row:
    Code:
    Option Explicit
    
    Private Sub Worksheet_Change(ByVal Target As Range)
    
       Dim lRow As Long
       Dim lCol As Long
       Dim lClearCol As Long
    
       If Not Intersect(Target, Range("O:O", "Q:Q")) Is Nothing Then
       
         Application.EnableEvents = False
         lRow = Target.Row
         lCol = Target.Column
         lClearCol = lCol + IIf(lCol = 15, 2, -2)
         Cells(lRow, lClearCol).ClearContents
         Application.EnableEvents = True
         
       End If    'Not Intersect
       
    End Sub      'Worksheet_Change
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,546
    Thanks
    138
    Thanked 11 Times in 11 Posts
    Hi Guys



    Thanks very much for your help. It is most appreciated

Posting Permissions

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