Excel Guides

Sorting Data on Protected Worksheets in Excel

There are a few different ways that you can sort data on protected worksheets in Excel. You can use the built-in sorting features, or you can write your own code to sort the data.

Using the Built-In Sorting Features

Excel has a few built-in features that you can use to sort data on protected worksheets. The first is the Sort button on the Data tab of the ribbon. This will bring up the Sort dialog box, where you can choose how to sort your data.

You can also use the Filter button on the Data tab of the ribbon to filter your data. This will bring up the Filter dialog box, where you can choose which columns to filter and what criteria to use for filtering.

Writing Your Own Code to Sort Data

Private Sub Worksheet_Change(ByVal Target As Range)<br /><br />If Target.Address = "$A$1" Then<br /><br /> Dim lastrow As Long<br /> Dim i As Long<br /> Dim j As Long<br /> Dim temp As String<br /> Dim temp1 As String<br /> <br />"find last row with data in column A"<br />"assumes headers in row 1"<br />"change 2 to 1 if no headers"<br />"change 11 to desired column if not column A" & vbNewLine &"lastrow = Cells(Rows.Count, " &"A" &" ).End(xlUp).Row" &" " &"for i = 2 to lastrow - 1" &" " &"for j = i + 1 to lastrow" &" " &"if Cells(i, 1).Value > Cells(j, 1).Value then" &" " &"temp = Cells(i, 1).Value" &" " &"temp1 = Cells(j, 1).Value" &" " &"Cells(i, 1).Value = temp1" &vbNewLine &vbNewLine &vbTab &vbTab &vbTab + vbTab + vbTab + vbTab + vtCrLf + vtCrLf + vtCrLf+ vtCrLf+vtCrLf+vtCrLf+vtCrLf+vtCrLf+vtCrLf+vtCrLf+vtCrLf+vtCrLf+vtCrLf++ _ LCase("Cells") + "(j, 1).Value = temp" + vtCrlf + vtCrlf + "End If" + vtcrlf + "Next j" + vtcrlf + "Next i" + vtcrlf + vtcrlf + LCase("End Sub") , _ strTemp) 'LCase changes all letters in string to lowercase End If
End Sub
"assumes headers in row 1""change 2 to 1 if no headers""change 11 to desired column if not column A""lastrow = Cells(Rows.Count,"A").End(xlUp).Row""for i = 2 to lastrow - 1""for j = i + 1 to lastrow""if Cells(i,1).Value > Cells(j,1).Value then""temp = Cells(i,1).Value""temp1 = Cells(j,1).Value""Cells(i,1).Value= temp1""Cells(j,1).Value= temp""End If""Next j ""Next i ""End Sub";

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.