Problem with SORT macro?

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
679
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have an issue with this macro but don't know why.
My table headers are all in Row 9 and I have frozen the pane just below it. I can select any cell from A10:J10 and down, run the macro and it sorts the cell's row ascending/descending taking the rest of the table with it. It all works fine but it won't sort anything below Row 301 - any ideas why??
Thanks

Code:
Sub Macro2()
    Dim Rng As Range, col As Long, Rw As Long
    Static Pos As Integer
    Pos = IIf(Pos = xlAscending, xlDescending, xlAscending)
    col = Selection.Column
    Rw = Range("A10").CurrentRegion.Rows.Count
    Set Rng = Range("A10:J" & Rw)
    If Rng.Count > 1 Then Rng.Sort Cells(1, col), Pos
End Sub
 
Last edited:

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
try changing:

Code:
 Rw = Range("A10").CurrentRegion.Rows.Count
to
Code:
Rw = Cells(Rows.Count, "A").End(xlUp).Row
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top