Simple VBA code required to clear cell contents

[paulfisher]

I have a very large spreadsheet that requires processing soon.
The records in Column AD are drop down dependent on the answers to the same row in column AE.

I am only a VBA beginner and I have tried, but not yet succeeded, to write/find some VBA that will clear the related row entry in column AD if column AE is altered.

I have tried a code that works but only on a single row - say if AE2 is altered it clears AD2 and I did for a while have some code that if you alter any row in AE would clear EVERY row in AD however this is not quite what I want.

If I for example alter AE1965 I would like AD1965 cleared but not affect AD1:AD1964 or AD1966:AD2500.

Please help me!

Paul

 

[Andrew Poulsom]

Welcome to MrExcel.

Try this in the module for the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Cell As Range
    If Application.Intersect(Target, Columns("AE")) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo Cleanup
    For Each Cell In Target.Cells
        If Cell.Column = Range("AE1").Column Then
            Cell.Offset(0, -1).ClearContents
        End If
    Next Cell
Cleanup:
    Application.EnableEvents = True
End Sub

 

[paulfisher]

A working response within 10 minutes - brilliant, thanks.
I guess need to refer to the off-set if I wish to alter the column order...

Many thanks!