Help Adjusting Macro

[ExcelPadawan]

Hi guys, I have a macro running on a worksheet I am building. Right now it is set to clear a range of drop down list cells in one column based on a selection changing in a single drop down cell for that same column.

In this case it means if cell C11 is changed in any way, C19-C20 will automatically go blank. I would like this same formula applied for each column, so if D11 is changed in any way, D19-D30 will go blank, and so on and so forth.

Below is my current code:

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False
If Intersect(target, Range("C11")) Is Nothing Then Exit Sub
Range("C19:C30").ClearContents
End Sub

I've been playing with this code and can't figure out how to apply it so it functions in the same way but independently for each column, so changes in C will only affect column C, changes in D will only affect column D, etc.

Please help, and thank you!

 

[JLGWhiz]

and so on and so forth.

The above statement is not a valid VBA parameter. When specifying parameters of operation, the specific limitations should be spelled out. It is assumed you want this event code to apply to any column with data where the cell in row 11 is changed.

Private Sub Worksheet_Change(ByVal target As Range)
    If Intersect(target, Rows(11)) Is Nothing Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
        With ActiveSheet
            Range(.Cells(19, target.Column), .Cells(30, target.Column)).ClearContents
        End With
    Application.ScreenUpdating = True
    Application.EnableEvents = True
 End Sub

 

[ExcelPadawan]

This worked thank you so much! I wish I were this proficient... I did some research online, but it's difficult to re-purpose example formulas when you are trying to do something very specific without background knowledge and it's not spelled out for you.

Please pardon my ignorance, but can you recommend any resources to expand my knowledge in building macros/VBA? An online course or a book that would resonate with a philistine such as myself?

Thank you for your insight; I truly appreciate your response. It works! Thank you again; I am thrilled!

 

[hiker95]

ExcelPadawan,

Nice handle - we have all started there

can you recommend any resources to expand my knowledge in building macros/VBA?

See if something, in the below link(s), of my most up to date list will help you:

Training / Books / Sites as of 1/17/2015

http://www.mrexcel.com/forum/excel-questions/829893-macros.html#post4048295

 

[ExcelPadawan]

Jackpot! Thank you!

 

[JLGWhiz]

This worked thank you so much! I wish I were this proficient... I did some research online, but it's difficult to re-purpose example formulas when you are trying to do something very specific without background knowledge and it's not spelled out for you.

Please pardon my ignorance, but can you recommend any resources to expand my knowledge in building macros/VBA? An online course or a book that would resonate with a philistine such as myself?

Thank you for your insight; I truly appreciate your response. It works! Thank you again; I am thrilled!

You're welcome,
As hiker95 says, we have all been there and some of us, me included, haven't gone that much further along. But the elephant can be eaten one bite at a time.
Regards, JLG

 

[hiker95]

ExcelPadawan,

You are very welcome. Glad we could help.

And, come back anytime.

 

[ExcelPadawan]

So you guys helped me with the below formula to suit my purposes:


Private Sub Worksheet_Change(ByVal target As Range)
If Intersect(target, Rows(11)) Is Nothing Then Exit Sub
Application.EnableEvents = False
Application.ScreenUpdating = False
With ActiveSheet
Range(.Cells(19, target.Column), .Cells(30, target.Column)).ClearContents
End With
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub


I was wondering if there was a way to program a command into this formula to undo changes to the cell by pressing ctrl+z. If not it's not a big deal but I was just curious if it's doable. Thanks!