sort macro

  • Thread starter Thread starter Jean-Marie
  • Start date Start date
J

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,
 
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.
 
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
 
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"???
 
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
 
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
 
Thank you Don. It works!

Don Guillett said:
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)


.
Click to expand...
 
Back
Top