Can VBA on a sheet do this?

[Russk68]

Hi All,

I want to clear contents of A1:F1 by selecting "Clear" from G1.
G1 is a list "Select","Clear"

So by selecting "Clear" from G1 would clear contents from A1:F1.

And then by selecting "Clear" anywhere in column G would also clear the same row in columns A:F

I'm trying to avoid creating a module for each line if possible and wondering if this can be coded in the sheet

Thanks!

 

[Joe4]

You can do that with a Worksheet_Chang event procedure.

Go to the sheet you want to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste the code in the resulting VB Editor window:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("G:G"))
    
    For Each cell In rng
        Application.EnableEvents = False
        If cell.Value = "Clear" Then
            Range(Cells(cell.Row, "A"), Cells(cell.Row, "F")).ClearContents
        End If
        Application.EnableEvents = True
    Next cell

End Sub

It will run automatically whenever a value of "Clear" is entered/selected in column G.

 

[rpaulson]

try this on a copy of your File:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Selection.Count = 1 Then
        If Not Intersect(Target, Range("G:G")) Is Nothing Then
            If ActiveCell = "Clear" Then
                r = ActiveCell.Row
                Range("A" & r & ":F" & r).ClearContents
             End If
        End If
    End If
End Sub

hth,
Ross

Joe beat me to it.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/30/2018  4:31:09 PM  EDT
If Not Intersect(Target, Range("G:G")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "Clear" Then Target.Offset(, -6).Resize(, 6).ClearContents
End If
End Sub

 

[Russk68]

Thank you all so much! I am off and running!

 

[My Aswer Is This]

Glad we were able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank you all so much! I am off and running!