Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 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
    2,832
    Thanks
    136
    Thanked 484 Times in 461 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 14:27.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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,641
    Thanks
    115
    Thanked 652 Times in 594 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,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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,423
    Thanks
    126
    Thanked 5 Times in 5 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
  •