MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Protecting and Sorting Cells

Posted by Brian Halcrow on May 29, 2001 2:11 AM


I have a spreadsheet that in the first seven or eight cells of a row I have input cells and in the remaining cells are formulaes. There can be up to 700 rows of data in each sheet.

I can protect the formulaes so that they cannot be deleted or altered but I cannot then sort the rows in to a specific order.

Does anyone know how I would be able to sort as and when required but still have certain cells protected?



Posted by Dave Hawley on May 29, 2001 2:34 AM

Hi Brian

To keep your Protection and still be able to sort you would need VBA.

Sub SortAndProtect()
ActiveSheet.Unprotect Password:="secret"

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

ActiveSheet.Protect Password:="secret"
End Sub

This code assumes you have selected your range to sort(including headings) and you want to sort by the first Column in your selection (A2).


OzGrid Business Applications