Problem with SORT macro?

cjcass

Well-known Member
Joined
Oct 27, 2011
Messages
683
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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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,223,888
Messages
6,175,213
Members
452,618
Latest member
Tam84

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