Code Not Working Properly Anymore

[zeetoe04]

I've been using the below code for my spreadsheet to sort this group
of data. It was working fine until today - not sure what even happened
that would change it. What it should be doing is going ot this tab,
unprotecting it, selecting all of the data (Columns A-G always, its
the rows down that fluctuates), sorting it by F3, then reprotecting
the sheet.

Now its only sorting the first four columns (A-D). Any assistance?



Sheets("List").Select
ActiveSheet.Unprotect
Range("A3").Select
Range(ActiveCell.End(xlDown), ActiveCell.End(xlToRight)).Select
Selection.Sort Key1:=Range("F3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Range("a1").Select
ActiveSheet.Protect

 

[Susan]

possible: cells E, F, & G are now empty, so your range is only being
set through column D?

susan

 

[Chip Pearson]

Try something like the following:

Dim StartRow As Long
Dim EndRow As Long
Dim LastRow As Long
Dim L As Long
Dim N As Long
Dim WS As Worksheet

Set WS = Worksheets("List")
StartRow = 1 '<<<<< adjust as necessary
With WS
.Unprotect
For N = 1 To 7 ' cols A -> G
LastRow = .Cells(.Rows.Count, N).End(xlUp).Row
If LastRow > L Then
L = LastRow
End If
Next N
.Range(.Cells(StartRow, "A"), .Cells(L, "G")).Sort _
key1:=.Cells(StartRow, "F"), order1:=xlAscending
.Protect
End With

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)