Remove Values From Columns When Another Column Has A Value

[DMO123]

Hi, i have the below macro which removes values in column H when column I has a "Yes" in. is there a way to change this into an automatic VBA that runs in the background?

Sub Remove_Column_H_Values()'
' Remove_Column_H_Values Macro
'


'
Application.Goto Reference:="R7C9"
ActiveSheet.ListObjects("Table11").Range.AutoFilter Field:=8, Criteria1:= _
"<>"
Application.Goto Reference:="R7C8"
Range("H8").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Application.Goto Reference:="R7C9"
ActiveSheet.ListObjects("Table11").Range.AutoFilter Field:=8
Range("I8").Select
End Sub

 

[gallen]

So if the user set the value in column "I" to "YES" you want it to automatically clear the value in column "H"?

This uses the change event of the sheet to do that:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 9 Then 'Column I
        If UCase(Target) = "YES" Then Target.Offset(0, -1).ClearContents
    End If
End Sub

 

[DMO123]

works perfectly - thank you!

 

[DMO123]

Question on this - i want to do this in another column on the same sheet how do i run two of these i keep getting errors for running two private subs. the other column i would like to do this to is if Yes is entered in column M delete L - thanks!

 

[gallen]

In your second instance, it's the same as the first in that "If the column has 'Yes' then clear the previous column". Therefore this works fine:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 9 Or Target.Column = 13 Then 'Column I or column M
        If UCase(Target) = "YES" Then Target.Offset(0, -1).ClearContents
    End If
End Sub

 

[DMO123]

That simple! wow - thank you so much!

 

[DMO123]

@gallen another question on this code. a formula updates the cells to trigger this code however, the code wont run unless i tab out of the updated cell. is there a way to get this to update when say moving to the next sheet etc. as these are hidden column so wouldn't be able to tab out all the time.

thank you!

 

[gallen]

Sorry for delay, I've been away

I'm a little confused. The original code needs to check if the user has changed a value in Column 9 or column 13. Therefore the data should always be up to date.

You could create a separate procedure to loop through all values in both columns and clear any contents in adjacent cells when it finds a "YES" - then call this procedure when exiting the sheet but as I say, it should be up to date.

 

[DMO123]

@gallen no problem, its now solved, the code wasn't updating for cells that had a formula only when i selected the cells that had been updated. so i asked around for some support and the code has been updated to factor this in other than that your code worked perfectly. the updated code is:

Private Sub Worksheet_Calculate()

Dim lr As Long, arr, arrCol, i As Long, j As Long, rng As Range


lr = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row


arr = Range("A1:T" & lr)
arrCol = Array(11, 16, 17, 19, 20)


For i = LBound(arr) To UBound(arr)
    For j = LBound(arrCol) To UBound(arrCol)
        If LCase(arr(i, arrCol(j))) = "yes" Then
            If Not rng Is Nothing Then
                Set rng = Union(rng, Cells(i, arrCol(j) - 1))
            Else
                Set rng = Cells(i, arrCol(j) - 1)
            End If
        End If
    Next
Next


Application.EnableEvents = False
If Not rng Is Nothing Then rng.ClearContents
Application.EnableEvents = True


End Sub


Private Sub Worksheet_Change(ByVal Target As Range)


If Not Intersect(Target, Union(Columns(11), Columns(16), Columns(17), Columns(19), Columns(20))) Is Nothing Then
    Worksheet_Calculate
End If


End Sub