View Full Version : Repeat VBA code for each cell
j.stitt1
08-16-2018, 07:58 AM
Hello,
I have this code that works for the cell intended but i would like it available for a range of cell C4 thru C103.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("C4")) Is Nothing Then
If Range("C4") = "" Then Range("D4:V4").ClearContents
If Range("C4") <> "" Then Range("D4:V4").ClearContents
End If
End Sub
Any help would be grateful
Thanks
Jeffrey
Paul_Hossler
08-16-2018, 08:10 AM
1. You should use the[#] icon to insert CODE tags and then paste ypur macro between them
2. These don't make sense since D-V get cleared regardless of the C
If Range("C4") = "" Then Range("D4:V4").ClearContents
If Range("C4") <> "" Then Range("D4:V4").ClearContents
3. You should disable event handling when you're changing the WS from inside an event handler
4. GUESSING that you want D-V changed in the row where C is. This clears D-V regardless of the contents of the corresponding C
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
If Intersect(Target, Range("C4:C103")) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Intersect(Target, Range("C4:C103")).Cells
r.Offset(0, 1).Resize(1, 19).ClearContents
Next
Application.EnableEvents = True
End Sub
j.stitt1
08-16-2018, 08:23 AM
this is why.
it may be incorrect but it seems to works.
If Range("C4") = "" Then Range("D4:V4").ClearContents (With is one if i use the delete key it clears my range but if i change the value of C4 it won't clear the range.)
If Range("C4") <> "" Then Range("D4:V4").ClearContents (With is one if i use the if i change the value of C4 it clears the range but if i use delete key it won't clear my range.)
j.stitt1
08-16-2018, 08:35 AM
Thank You!
but i would like to add a cell to clear. I see you are clearing 1 thru 19 cells after column C i also need to skip 20, 21 and clear 22.
how would i add?
p45cal
08-16-2018, 10:30 AM
either add directly after the
r.Offset(0, 1).Resize(1, 19).ClearContents
line, the following:
r.Offset(, 22).ClearContents
or
replace it with:
Union(r.Offset(, 1).Resize(, 19), r.Offset(, 22)).ClearContents
j.stitt1
08-16-2018, 12:21 PM
Awesome!!
Thank You!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.