Create a Dynamic Sort Range in a Macro.

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
Hi All,

I have a macro that creates individual sheets from a list, it then creates a Summary list.

Prior to running, the Macro, the data needs to be copied and Past Special Values over itself and then needs to be sorted to enable the split to work.

Currently, the copy Paste Special and Sort are undertaken manually. The following is the macro:-

VBA Code:
Sub PasteSpecial_Sort()
' This Macro Selects the Data range, copy’s the range and Past Special Values over itself. It then Sorts the data range using Column D as the sort Key.
Range("A2:T2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

' This area is the Sort Part

ActiveWorkbook.Worksheets("Buylist").Sort.SortFields.Clear

ActiveWorkbook.Worksheets("Buylist").Sort.SortFields.Add Key:=Range("D3:D218"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Buylist").Sort

.SetRange Range("A2:T218")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With

End Sub

The problem with the macro is that the range will differ every year (I selected the range by holding the Shift+CTRL+Arrow Down.

Can the above be adapted to create the range using the Shift+CTRL+Arrow Down automatically?
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
Does row 2 contain your headers & if so is row 1 blank?
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
Fluff,

Both Row 1 and Row 2 contain Headers. However, Row 1 (A1) contains a year value and can be excluded from the Sort. Row 2 is the one with all the headers.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
Sub Kayslover()
   Dim UsdRws As Long
   
   With Sheets("Buylist")
      UsdRws = .Cells.Find("*", , xlFormulas, , xlByRows, xlNext, , , False).Row
      .UsedRange.Value = .UsedRange.Value
      .Range("A2:T" & UsdRws).Sort .Parent.Range("D2"), xlAscending, , , , , , xlYes
   End With
End Sub
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Fluff,

When I run it, I get “Run Time error 438, Object doesn’t support this property or method” on the line of code that says

Range("A2:T" & UsdRws).Sort .Parent.Range("D2"), xlAscending, , , , , , xlYes
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
The line is .Range("A2:T" & UsdRws).Sort .Parent.Range("D2"), xlAscending, , , , , , xlYes
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Oops, it should be
VBA Code:
Sub Kayslover()
   Dim UsdRws As Long
   
   With Sheets("Buylist")
      UsdRws = .Cells.Find("*", , xlFormulas, , xlByRows, xlNext, , , False).Row
      .UsedRange.Value = .UsedRange.Value
      .Range("A2:T" & UsdRws).Sort .Range("D2"), xlAscending, , , , , , xlYes
   End With
End Sub
 

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
Fluff,

Tried, you revised solution and all it seems to be doing is the Copy Special Values. It is not sorting
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,470
Office Version
  1. 365
Platform
  1. Windows
I really should test things first, it should be
VBA Code:
Sub Kayslover()
   Dim UsdRws As Long
   
   With Sheets("Buylist")
      UsdRws = .Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious, , , False).Row
      .UsedRange.Value = .UsedRange.Value
      .Range("A2:T" & UsdRws).Sort .Range("D2"), xlAscending, , , , , , xlYes
   End With
End Sub
 
Solution

Kayslover

Board Regular
Joined
Sep 22, 2020
Messages
136
Office Version
  1. 2013
Platform
  1. Windows
Fluff,

As expected, worked a treat. Appreciated your kind assistance.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,427
Messages
5,624,736
Members
416,046
Latest member
Elliottj2121

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
Top