I have to sort every value after row 9 which contains "Start" and before last text string "End" (two rows above "End") in three different sheets. This code works if "End" is hidden, but I would like to do this when "End" is shown instead. Otherwise "End" is sorted with the values after row 9.
I want "End" (the last text string) to stay at the same place after the sorting, not to be affected by the code.
Sub SortYear()
Dim xlSort As XlSortOrder
Dim e
For Each e In Array(Array("SheetA"), Array("SheetB"), Array("SheetC"))
With Sheets(e(0))
.Unprotect msPASSWORD
If (StrComp(UCase(.Range("A10").Value), UCase(.Range("A11").Value)) > 0) Then
xlSort = xlAscending
Else
xlSort = xlDescending
End If
With .Range("A10:XFD1048576")
.Sort Key1:=.Range("A10"), Order1:=xlSort, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
.Protect Password:=msPASSWORD, DrawingObjects:=False, Contents:=True, Scenarios:=True
End With
Next e
ActiveSheet.EnableSelection = xlNoRestrictions
Sheets("SheetA").Activate
End Sub
Bookmarks