Hello,
I would like to write a macro to sort data from a list which is getting
longer and longer. How to tell the macro to go to the last non empty cell?
Thanks in advance,

Hi,

You are always better posting your code. The line below sets a range of the
used cells in column A

Set myrange = Range("A1:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)

If you wanted to start in (say) A4 then you would change it to this

Set myrange = Range("A4:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jean-Marie" wrote:

Hello,
I would like to write a macro to sort data from a list which is getting
longer and longer. How to tell the macro to go to the last non empty cell?
Thanks in advance,

The best way is to set up a self adjusting named range
On the sheet desiredInsertnamedefinename it SortRangein the formula box
=offset($a$1,0,0,counta($a:$a),12)
Look in the help index for OFFSET for more info

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jean-Marie" wrote in message
...
Hello,
I would like to write a macro to sort data from a list which is getting
longer and longer. How to tell the macro to go to the last non empty cell?
Thanks in advance,

Thanks Mike for this speedy answer.
Since I'm preety new to this, what do you mean by "You are always better
posting your code"???

"Mike H" wrote:

Hi,

You are always better posting your code. The line below sets a range of the
used cells in column A

Set myrange = Range("A1:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)

If you wanted to start in (say) A4 then you would change it to this

Set myrange = Range("A4:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jean-Marie" wrote:

Hello,
I would like to write a macro to sort data from a list which is getting
longer and longer. How to tell the macro to go to the last non empty cell?
Thanks in advance,

Hi,

I assumed you had some code that wasn't working the way you wanted it to do
so the best way to get and answer is to include a copy of that code with your
question.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jean-Marie" wrote:

Thanks Mike for this speedy answer.
Since I'm preety new to this, what do you mean by "You are always better
posting your code"???

"Mike H" wrote:

Hi,

You are always better posting your code. The line below sets a range of the
used cells in column A

Set myrange = Range("A1:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)

If you wanted to start in (say) A4 then you would change it to this

Set myrange = Range("A4:A" & Cells(Cells.Rows.Count, "A").End(xlUp).Row)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"Jean-Marie" wrote:

Hello,
I would like to write a macro to sort data from a list which is getting
longer and longer. How to tell the macro to go to the last non empty cell?
Thanks in advance,

Sometimes it is "easier" to declare a "List" ["Table" in "2007/2010"] and to
name it DATA, SORTRANGE, or what ever - the MAcrro should then be referred to
that 'Name'.
When using this kind of "List" it gets Dynamic when adding data to it.
Micky


"Don Guillett" wrote:

The best way is to set up a self adjusting named range
On the sheet desiredInsertnamedefinename it SortRangein the formula box
=offset($a$1,0,0,counta($a:$a),12)
Look in the help index for OFFSET for more info

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jean-Marie" wrote in message
...
Hello,
I would like to write a macro to sort data from a list which is getting
longer and longer. How to tell the macro to go to the last non empty cell?
Thanks in advance,


.

I like to pick out a column that always has data in it if the row is used. And
I use a row that always has data in it if the column is used (like headers in
row 1).

In this sample, I used column A and row 1:

Option Explicit
Sub Testme()

Dim LastRow as long
Dim LastCol as long
dim wks as worksheet
dim myRng as range

set wks = worksheets("somesheetnamehere")

with wks
lastrow = .cells(.rows.count,"A").end(xlup).row
lastcol = .cells(1, .columns.count).end(xltoleft).column

set myrng = .range("A1", .cells(lastrow, lastcol))
end with

with myrng
.sort key1:=.columns(1), order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
end with

End with

I like this technique from Debra Dalgleish's site:
http://contextures.com/xlSort02.html#Rectangles
Sort With Invisible Rectangles

Jean-Marie wrote:

Hello,
I would like to write a macro to sort data from a list which is getting
longer and longer. How to tell the macro to go to the last non empty cell?
Thanks in advance,

--

Dave Peterson

Thank you Don. It works!

"Don Guillett" wrote:

The best way is to set up a self adjusting named range
On the sheet desiredInsertnamedefinename it SortRangein the formula box
=offset($a$1,0,0,counta($a:$a),12)
Look in the help index for OFFSET for more info

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jean-Marie" wrote in message
...
Hello,
I would like to write a macro to sort data from a list which is getting
longer and longer. How to tell the macro to go to the last non empty cell?
Thanks in advance,


.

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jean-Marie" wrote in message
...
Thank you Don. It works!

"Don Guillett" wrote:

The best way is to set up a self adjusting named range
On the sheet desiredInsertnamedefinename it SortRangein the formula
box
=offset($a$1,0,0,counta($a:$a),12)
Look in the help index for OFFSET for more info

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Jean-Marie" wrote in message
...
Hello,
I would like to write a macro to sort data from a list which is getting
longer and longer. How to tell the macro to go to the last non empty
cell?
Thanks in advance,


.