How to know the address of the last cell
 

Hello,

_Example_

RANGE(\"A1:H44\").SORT KEY1:=RANGE(\"B2\"), ORDER1:=XLASCENDING,
HEADER:= _
XLGUESS, ORDERCUSTOM:=1, MATCHCASE:=FALSE,
ORIENTATION:=XLTOPTOBOTTOM, _
DATAOPTION1:=XLSORTNORMAL


My problem ; the last cell of this selection (H44) is increasing every
day (H45, H46 ...).

How can I calculate automatically the last cell ?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

How to know the address of the last cell
 

Hello

Please use the method .End(xlDown) of Range Object



"ChefChaudart" wrote in message
...
Hello,

_Example_

RANGE(\"A1:H44\").SORT KEY1:=RANGE(\"B2\"), ORDER1:=XLASCENDING,
HEADER:= _
XLGUESS, ORDERCUSTOM:=1, MATCHCASE:=FALSE,
ORIENTATION:=XLTOPTOBOTTOM, _
DATAOPTION1:=XLSORTNORMAL


My problem ; the last cell of this selection (H44) is increasing every
day (H45, H46 ...).

How can I calculate automatically the last cell ?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

How to know the address of the last cell
 

It is considered impolite to type all CAPS. Called "shouting"
You could use
x=cells(65536,"h").end(xlup).row
range("a1:h" & x)

or better yet you could create a defined name for the range that
automatically increases with each addition or subtraction.
insertnamedefinetype in a name like colHin the refers to box type
=offset($H$1,0,0,counta($h:$h),1)

"ChefChaudart" wrote in message
...
Hello,

_Example_

RANGE(\"A1:H44\").SORT KEY1:=RANGE(\"B2\"), ORDER1:=XLASCENDING,
HEADER:= _
XLGUESS, ORDERCUSTOM:=1, MATCHCASE:=FALSE,
ORIENTATION:=XLTOPTOBOTTOM, _
DATAOPTION1:=XLSORTNORMAL


My problem ; the last cell of this selection (H44) is increasing every
day (H45, H46 ...).

How can I calculate automatically the last cell ?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

How to know the address of the last cell
 

Use Range("A1").Sort

Excel should pick up the appropriate range. You don't need notation like
\"

--
Regards,
Tom Ogilvy

"ChefChaudart" wrote in message
...
Hello,

_Example_

RANGE(\"A1:H44\").SORT KEY1:=RANGE(\"B2\"), ORDER1:=XLASCENDING,
HEADER:= _
XLGUESS, ORDERCUSTOM:=1, MATCHCASE:=FALSE,
ORIENTATION:=XLTOPTOBOTTOM, _
DATAOPTION1:=XLSORTNORMAL


My problem ; the last cell of this selection (H44) is increasing every
day (H45, H46 ...).

How can I calculate automatically the last cell ?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/