How to know the address of the last cell

  • Thread starter Thread starter ChefChaudart
  • Start date Start date
C

ChefChaudart

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 ?
 
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.
insert>name>define>type in a name like colH>in the refers to box type
=offset($H$1,0,0,counta($h:$h),1)
 
Use Range("A1").Sort

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