Sort variable range of data

coop123

New Member
Joined
Dec 18, 2018
Messages
25
Office Version
  1. 365
Hi

I am trying to write a maco to sort on column A, each time the marco is to be run the range of data may be different.
The width can vary number of columns increases or decreases, length can vary number of row increase or decrease.

Can anyone give me a clue as to how to set dynamic ranges to cope with the above scenario.

Thank you in advance

Coop123
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
If you have no blank cells then you can use the currentregion.

VBA Code:
Range("A1").CurrentRegion.Sort Range("A1"), xlAscending, , , , , , xlYes
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,224
Office Version
  1. 365
Platform
  1. Windows
Assuming there are no completely blanks rows or columns in your data try
VBA Code:
Sub coop()
   Range("A1").CurrentRegion.Sort Range("a1"), xlAscending, , , , , , xlYes
End Sub
 

coop123

New Member
Joined
Dec 18, 2018
Messages
25
Office Version
  1. 365
Hi

Thank you for your replies.

My data does have blank columns, I want to keep the headings.

If you could give me another suggestion it would be very much appreciated.

Thank you.

Coop123
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Try the below (if it is the Active sheet)...

VBA Code:
Sub coop123()
    Dim lr As Long, lc As Long
    lr = ActiveSheet.Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    lc = ActiveSheet.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column

    Range(Cells(1, 1), Cells(lr, lc)).Sort Range("A1"), xlAscending, , , , , , xlYes
End Sub
 
Solution

coop123

New Member
Joined
Dec 18, 2018
Messages
25
Office Version
  1. 365
Hi Mark858

This has worked perfectly.

Thanks you for you help.

Coop123
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You're welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,126,904
Messages
5,621,571
Members
415,847
Latest member
nigeywigey

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