sort macro

[Jean-Marie]

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,

 

[Mike H]

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.

 

[Don Guillett]

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

 

[Jean-Marie]

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]

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.

 

[מיכ×ל (מיקי) ×בידן]

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

 

[Dave Peterson]

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]

Thank you Don. It works!

The best way is to set up a self adjusting named range
On the sheet desired>Insert>name>define>name it SortRange>in 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
(e-mail address removed)


.

 

[Don Guillett]

Glad to help