End(xlUp)

Hello,

Please could someone explain how End(xlUp) works?? I have also seen
End(xlUp) (2) as well, again I have no idea what it means. I have
searched this group and the various sites but I could not get any
satisfactory answers.

Regards,

Bharesh

Hi Bharesh,

End function is pretty simple, same thing that happens when you press
ctrl key and then up arrow key. (right, left and down also work.)

Lets consider, you have a data where you want to add some more stuff at
the end. How will you get to the Last cell in column A.

Here is how...
Sub xlups()
Range("A65536").End(xlUp).Select
End Sub

Range("A65536") is the last cell in column A.. this is very useful when
you want to create a database and add / delete records from the same.

the (2) is for offset... for example you want to select the cell in
column C on the next row of the last used row in column A, you will use

Range("A65536").End(xlup)(2,3).Select

Note... the offset works a bit differently here in this function, as
(1,1) is the "SAME" cell unlike the normal offset function.

Hope this helps... if you need any more help, mail me directly.

~Yogendra

End(xlUp) simply looks for the end cell, looking up the column relative to
where the start point is. That start point can be a specific cell (B1000),
or more dynamic (Cells(Rows.Count,"B"))

--

HTH

RP
(remove nothere from the email address if mailing direct)


"m4nd4li4" <[email protected]> wrote in message
news:[email protected]...
> Hello,
>
> Please could someone explain how End(xlUp) works?? I have also seen
> End(xlUp) (2) as well, again I have no idea what it means. I have
> searched this group and the various sites but I could not get any
> satisfactory answers.
>
> Regards,
>
> Bharesh
>

Hello,

(2) is shorthand for the range's Item Property. See the following:

http://www.cpearson.com/excel/cells.htm

It's actually a 2-d reference, but you can omit the 2nd argument.

Like most MS shipped classes, it's 1-based, so (1,1) refers to the top-left
cell in the reference, 2 moves down a row, 0 actually moves back a row, etc...

An exception to 1-based class rule would be in Access:

MsgBox DBEngine.Workspaces.Item(0).Databases.Item(0).Name

Or:

MsgBox DBEngine(0)(0).Name

The Range is an odd bird, most collections as we know them are 1-d...

Regards,
Nate Oliver

Many thanks people. This will help in a macro I'm writing.

Regards,

Bharesh

Originally Posted by Yogendra

Hi Bharesh,

End function is pretty simple, same thing that happens when you press
ctrl key and then up arrow key. (right, left and down also work.)

Lets consider, you have a data where you want to add some more stuff at
the end. How will you get to the Last cell in column A.

Here is how...
Sub xlups()
Range("A65536").End(xlUp).Select
End Sub

Range("A65536") is the last cell in column A.. this is very useful when
you want to create a database and add / delete records from the same.

the (2) is for offset... for example you want to select the cell in
column C on the next row of the last used row in column A, you will use

Range("A65536").End(xlup)(2,3).Select

Note... the offset works a bit differently here in this function, as
(1,1) is the "SAME" cell unlike the normal offset function.

Hope this helps... if you need any more help, mail me directly.

~Yogendra

Late I know, but damn good response sir! If more folks responded like you, I wouldn't have just wasted $90 on VBA books

This was very helpful. Thanks.

I add new data to a spreadsheet weekly and this allows me to put it on the row following the current data. Be aware that if data is added to only some columns of a formatted table, doing the same will not work in another column within the same row. I guess it considers an empty cell within a formatted table range to be "something". This happened after the table expanded to the additional row. When I tried adding data on another cell it put it one row below the newly expanded table. I simply put the offset to be 1 instead of 2 and it worked great.

Originally Posted by MarvinP

I just found another site that explains this at:
http://spreadsheetpage.com/index.php...e_size_ranges/

Thanks MarvinP. Good stuff.