Sort variable range of data

[coop123]

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

 

[MARK858]

If you have no blank cells then you can use the currentregion.

Range("A1").CurrentRegion.Sort Range("A1"), xlAscending, , , , , , xlYes

 

[Fluff]

Assuming there are no completely blanks rows or columns in your data try

Sub coop()
   Range("A1").CurrentRegion.Sort Range("a1"), xlAscending, , , , , , xlYes
End Sub

 

[coop123]

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]

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

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

 

[coop123]

Hi Mark858

This has worked perfectly.

Thanks you for you help.

Coop123

 

[MARK858]

You're welcome