Sorting UsedRange of a Worksheet

[RyanH]

How can I sort only the UsedRange of each Worksheet in a workbook? This is
what I have so far and it will not let me.

Sub Sort ()

For Each wks In Worksheets
wks.UsedRange.Sort Key1:=Range("L3"), Order1:=xlAscending, _
Key2:=Range("A3"), Order2:=xlAscending,
Header:=xlGuess
Next wks

End Sub

 

[Chip Pearson]

Ryan,

The problem is that when you specify the key as Range("L3"), that range
refers the the L3 on the *active* sheet, which is not going to be the wks
sheet. Try

Sub Sort()
For Each wks In Worksheets
With wks
.UsedRange.Sort Key1:=.Range("L3"), Order1:=xlAscending, _
Key2:=.Range("A3"), Order2:=xlAscending, Header:=xlGuess
End With
Next wks

End Sub

Note that there is a leading period before UsedRange,Range("A3"), and
Range("L3"). These are required for the With statement.


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

 

[Jim Cone]

Range("L3") and Range("A3") refer to the active sheet not the wks sheet.
Preface them with wks... wks.Range("L3") ...

-also-

It is possible that the two range references may not be part of the used range on every sheet.

Finally, declare the wks variable... Dim wks as Worksheet
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"RyanH"
wrote in message
How can I sort only the UsedRange of each Worksheet in a workbook? This is
what I have so far and it will not let me.

Sub Sort ()
For Each wks In Worksheets
wks.UsedRange.Sort Key1:=Range("L3"), Order1:=xlAscending, _
Key2:=Range("A3"), Order2:=xlAscending,
Header:=xlGuess
Next wks
End Sub

 

[RyanH]

I have a header row on the first 2 rows. In the header I have merged cells,
such as (A1:A2), (B1:B2), (C1:C2),....etc. I am running the code below and I
get an Error stating: "This operation requires merged cells to be
identically in size."

Is the code trying to sort the header row? I only want to sort the used
from row 3 and below.

Dim wks Worksheet

For Each wks In Worksheets
With wks
wks.UsedRange.Sort Key1:=wks.Range("L3"), Order1:=xlAscending, _
Key2:=wks.Range("A3"), Order2:=xlAscending,
Header:=xlGuess
End With
Next wks

End Sub

 

[Jim Cone]

The "header" in a sort range can only be the top row of the range (one row only).
So with the top two rows merged, Excel cannot separate the two rows..
You will live a happier life if you avoid the use of merged cells.
You could unmerge all cells before sorting and then merge them again
after the sort if that becomes necessary.
The following code simply sorts the range starting in cell A3 and tells Excel
there is no header row (Header:=xlNo) ...

Sub Sort_R1()
Dim wks As Worksheet
Dim rng As Range
For Each wks In Worksheets
Set rng = wks.Range("A3", wks.Cells.SpecialCells(xlCellTypeLastCell))
rng.Sort Key1:=wks.Range("L3"), Order1:=xlAscending, _
Key2:=wks.Range("A3"), Order2:=xlAscending, Header:=xlNo
Next wks
Set rng = Nothing
Set wks = Nothing
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - check out "Special Sort")




"RyanH"
wrote in message
I have a header row on the first 2 rows. In the header I have merged cells,
such as (A1:A2), (B1:B2), (C1:C2),....etc. I am running the code below and I
get an Error stating: "This operation requires merged cells to be
identically in size."
Is the code trying to sort the header row? I only want to sort the used
from row 3 and below.

Dim wks Worksheet
For Each wks In Worksheets
With wks
wks.UsedRange.Sort Key1:=wks.Range("L3"), Order1:=xlAscending, _
Key2:=wks.Range("A3"), Order2:=xlAscending,
Header:=xlGuess
End With
Next wks
End Sub