how to record a macro that includes "ctrl+shift+pagedown"

jefals

New Member
Joined
Feb 12, 2025
Messages
2
Office Version
  1. 2024
Platform
  1. Windows
I recorded a macro to sort my worksheet, which starts out with 121 rows.
After clicking "record macro", I move to cell A1, then do "shift ctrl page down" to select column A. Then "shift ctrl end (right arrow)" to get my columns. I then enter my sort criteria (cols, D, A, B), and that's it.
I've pasted the code that gets generated below. Notice the hard cell references for "D2:D121", "A2:A121" and "B2:B121". However, if I add more rows, it looks like this will still only sort the first 121 rows.
How can I solve this?
Thanks!

Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveWorkbook.Worksheets("February").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("February").Sort.SortFields.Add2 Key:=Range( _
"D2:D121"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("February").Sort.SortFields.Add2 Key:=Range( _
"A2:A121"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveWorkbook.Worksheets("February").Sort.SortFields.Add2 Key:=Range( _
"B2:B121"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("February").Sort
.SetRange Range("A1:H121")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
 
try like this, based on ur codes

VBA Code:
Sub jefals()

    Dim ws As Worksheet, ls As Long
    Set ws = ThisWorkbook.Sheets("February") 'ur sheet name
    ls = ws.Cells(Rows.Count, "A").End(xlUp).Row 'To get last row

With ws
    .Range("A1").CurrentRegion.Select
    .Sort.SortFields.Clear
  
    .Sort.SortFields.Add2 Key:=Range( _
        "A2:A" & ls), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
      
    .Sort.SortFields.Add2 Key:=Range( _
        "B2:B" & ls), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
      
    .Sort.SortFields.Add2 Key:=Range( _
        "D2:D" & ls), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
      
        With .Sort
            .SetRange Range("A1:H" & ls)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
End With
End Sub
 
Upvote 0
Solution
That did it. Thanks! ( Well, almost. I wanted to sort by cols, D, A, and B, and this one sorted by A, B, then D. But it was easy enough for me to see from your code that I just needed to move that last ".Sort.Sortfields" bit to the top)
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,919
Members
453,767
Latest member
922aloose

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