PDA

View Full Version : Sort ascending with expand selection



sujittalukde
04-22-2008, 04:57 AM
I have reocrded a macro to sort column B with expand selection. The code is as follows


Sheets("Master").Select
Range("B1").Select
ActiveSheet.Unprotect
Columns("B:B").Select
Range("A1:AZ79").Sort Key1:=Range("B1"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("B1").Select

I want to use the same code in a loop but the problem is that the range used in the recorded code uses the actual colum no used as Range("A1:AZ79").
But this will not be fixed for all sheets . For some sheets last column may be M or Z etc.
How can I adjust the code so that it will take the last used column for every sheet?

Bob Phillips
04-22-2008, 05:21 AM
With Sheets("Master")

.Unprotect
lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
.Range("A1").Resize(79, lastcol).Sort Key1:=Range("B1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With