Help editing a current piece of VBA

[Juddman]

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

[June7]

Suggest you provide file for analysis. Follow instructions at bottom of my post.

[Juddman]

Suggest you provide file for analysis. Follow instructions at bottom of my post.

Thanks for replying

Please see attached workbook with the VBA code on the index sheet. The sheets are unprotected but I would like to protect (no password) the cells with formulas in but still clear the data from the range of cells in both sheets.

[June7]

I am confused. Column E on Searches and Column F on Tests are all formulas so why are you clearing their content?

If a range has formulas and data and you want to clear only data, review https://stackoverflow.com/questions/4430755/excel-clearcontent-preserving-formulas

I added 10 rows to top of Tests.
Adjust code:
s2Row = ((Int(Target.Row + 4) / 2) * 3) - 1


Protect sheets before saving then in code you Unprotect and Protect.

ws2.Unprotect
...code to edit
ws2.Protect

[Juddman]

I am confused. Column E on Searches and Column F on Tests are all formulas so why are you clearing their content?

If a range has formulas and data and you want to clear only data, review https://stackoverflow.com/questions/4430755/excel-clearcontent-preserving-formulas

I added 10 rows to top of Tests.
Adjust code:
s2Row = ((Int(Target.Row + 4) / 2) * 3) - 1


Protect sheets before saving then in code you Unprotect and Protect.

ws2.Unprotect
...code to edit
ws2.Protect



When someone leaves and a new person arrives the date will change in the cells in these columns. The aim is to clear the data down ready for new a new schedule of dates and data to be entered. When the sheets were protected the previous code didn't do anything.

Thanks for the info, I will take a look.

[June7]

Then you need to clear data from cells referenced by those formulas.

[Juddman]

Then you need to clear data from cells referenced by those formulas.

I realised that I put the columns down, but I've amended the code accordingly and everything works great. Thanks for your help. I just need to add the unprotect and protect parts of code now.