Juddman
12-15-2023, 01:31 PM
Evening all,
I need some help editing some code (not mine, as I'm a complete newbie at this) in a workbook that is contained within sheet0 ("index"). I'm hoping someone is able to edit the code in a couple of ways if it is possible:
1. To make the code so it works and only clears unprotected cells within the ranges on both sheets
2. On ws2 ("Tests") I need to add some rows at the top of the sheet so I need the cells to be cleared start at Row 13 and not row 4. I'm going to assume this is the Target.Row line in the code.
Happy to explain further if needed but I'm hoping the code means something to someone, as I'm out of my depth.
Thanks in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long, s1Row As Long, s2Row As Long
Dim rng As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Searches")
Set ws2 = Sheets("Tests")
Application.EnableEvents = False
On Error GoTo SetMeFree:
If Not Intersect(Target, Range("E6:E41")) Is Nothing And Target.Count = 2 And Range("E" & Target.Row).Cells(1).Value = "" Then
s1Row = Int((Target.Row - 2) / 2) + 5
s2Row = ((Int(Target.Row - 2) / 2) * 3) - 2
Set rng = ws1.Range("E" & s1Row)
For c = 7 To ws1.Range("BE" & s1Row).Column Step 2
Set rng = Union(rng, ws1.Cells(s1Row, c))
Next c
rng.ClearContents
ws2.Range("F" & s2Row - 1 & ":F" & s2Row + 1).ClearContents
ws2.Range("J" & s2Row & ":DE" & s2Row + 1).ClearContents
End If
SetMeFree:
Application.EnableEvents = True
End Sub
I need some help editing some code (not mine, as I'm a complete newbie at this) in a workbook that is contained within sheet0 ("index"). I'm hoping someone is able to edit the code in a couple of ways if it is possible:
1. To make the code so it works and only clears unprotected cells within the ranges on both sheets
2. On ws2 ("Tests") I need to add some rows at the top of the sheet so I need the cells to be cleared start at Row 13 and not row 4. I'm going to assume this is the Target.Row line in the code.
Happy to explain further if needed but I'm hoping the code means something to someone, as I'm out of my depth.
Thanks in advance.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Long, s1Row As Long, s2Row As Long
Dim rng As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Sheets("Searches")
Set ws2 = Sheets("Tests")
Application.EnableEvents = False
On Error GoTo SetMeFree:
If Not Intersect(Target, Range("E6:E41")) Is Nothing And Target.Count = 2 And Range("E" & Target.Row).Cells(1).Value = "" Then
s1Row = Int((Target.Row - 2) / 2) + 5
s2Row = ((Int(Target.Row - 2) / 2) * 3) - 2
Set rng = ws1.Range("E" & s1Row)
For c = 7 To ws1.Range("BE" & s1Row).Column Step 2
Set rng = Union(rng, ws1.Cells(s1Row, c))
Next c
rng.ClearContents
ws2.Range("F" & s2Row - 1 & ":F" & s2Row + 1).ClearContents
ws2.Range("J" & s2Row & ":DE" & s2Row + 1).ClearContents
End If
SetMeFree:
Application.EnableEvents = True
End Sub