VBA: Clear Contents of Cells after Cell Change

[TheMAP]

Hi everyone!

I know I am way overthinking this, but I can't figure out a more efficient string of code. I want to be able to clear the contents of a range of cells in the same row as a cell that changes. So I change cell B10 and it contents are cleared in cells C10:Q10. I want to be able to run this code as a button on a UserForm, so a Worksheet_Change sub won't work (in fact: this is all part of that kind of sub). My horribly inefficient code is below.

Any help is appreciated. Thanks!

---

Private Sub SubmitButton_Click()

Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 3).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 4).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 5).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 6).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 7).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 8).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 9).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 10).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 11).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 12).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 13).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 14).Address).Offset(-1, 0).ClearContents
Worksheets("Play-by-Play - Redux").Range(Cells(Selection.Row, 15).Address).Offset(-1, 0).ClearContents


End Sub

---

 

[BobUmlas]

Hi everyone!

I know I am way overthinking this, but I can't figure out a more efficient string of code. I want to be able to clear the contents of a range of cells in the same row as a cell that changes. So I change cell B10 and it contents are cleared in cells C10:Q10. I want to be able to run this code as a button on a UserForm, so a Worksheet_Change sub won't work (in fact: this is all part of that kind of sub). My horribly inefficient code is below.

Any help is appreciated. Thanks!

---

Private Sub SubmitButton_Click()


Worksheets("Play-by-Play - Redux").Cells(Selection.Row-1, 3).Resize(1,13).ClearContents


End Sub