Results 1 to 6 of 6
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,468
    Thanks
    132
    Thanked 7 Times in 7 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,045
    Thanks
    145
    Thanked 541 Times in 516 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,562
    Thanks
    384
    Thanked 1,480 Times in 1,346 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,727
    Thanks
    125
    Thanked 685 Times in 622 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,562
    Thanks
    384
    Thanked 1,480 Times in 1,346 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,468
    Thanks
    132
    Thanked 7 Times in 7 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
  •