Naming a range for specific users to edit

[daycartes]

Hi folks, daycartes here. My, this is a busy forum.
I am engaging on a rather large spreadsheet that is going to have 10-15 users. I want to use the "Allow Users to Edit Ranges" function.
Now, I have already studied the named range advice, however the range that I want to allow is not in a complete block but spans several non contiguous rows eg row 6, row 12, row 194 and will include about 30 rows from within up to about 400 rows. The range is not random but depends on another column so as in the example above row 6,12 and 194 will be chosen because they correspond to DS in column 4 rows 6,12 and 194.
I could probably separate the ranges in the "Allow Users to Edit Ranges" however the rows could change over time so it needs to be accomplished dynamically.I think that I need to use VBA.
I can select the sheet using VBA and also filter out the column containing DS so that I am left with the non contiguous rows but I need the VBA code to select the appropriate range.
Does anyone have the VBA code for "Allow Users to Edit Ranges"?
Many thanks in advance.

 

[MandeepBaluja]

Sub Allow_Edit_Range_Rows_Dynamically()

'Note:-Title should always be unique but not contain a Dollar sign that's I put this
' Title:=Replace(Cells(i, 1).Address, "$", "")

Dim wksOne As Worksheet
strPwd1 = InputBox("Enter Password")

Set wksOne = Application.ActiveSheet
Dim temparray As Variant
' Protect the worksheet
'wksOne.Unprotect

temparray = Range("I1:I5") 'Range which contains Row number to allow

For i = 1 To 10
For j = LBound(temparray) To UBound(temparray)
If Cells(i, 1).Row = temparray(j, 1) Then
' MsgBox Replace(Cells(i, 1).Address, "$", "")
wksOne.Protection.AllowEditRanges.Add _
Title:=Replace(Cells(i, 1).Address, "$", ""), _
Range:=Rows(i), _
Password:=strPwd1

End If
Next
Next
wksOne.Protect

End Sub

 

[daycartes]

Thanks for your speedy response Mandeep. I opened up a new workbook and copied that into a new procedure which i then ran. The code worked in that it protected the sheet, however, if I understood what you sent me then cells i1:i5 should be editable but that does not seem to be the case. I think that we are nearly there though. Would you mind checking it out to see if the same happens for you. I'm new to VBA but have quite a bit of experience in Delphi. Thanks again

 

[MandeepBaluja]

You have asked for rows to allowedit and others to be locked without editing, In I1:I5 or I1:I200 you can put your row numbers in this range, this macro will allow those rows to edit dynamically selecting one by one and allowing it.Hope now you got that.

I1:I5 put 5,8,9,10,12 than these rows will be Allowed to Edit only

 

[daycartes]

Thanks for replying. Unfortunately despite putting various data into I1:I5 as you suggest the whole worksheet remains locked.