Hi Everybody,

Does anyone know a way to select the last used cell in a column by using
macro? For example, there is a range filled with data from "A1" to "A30", but
the closing cell is always changing (A32,A36...). I would like to find the
last data in column "A" by using VLOOKUP automatically.

Thank you in advance for your help,

sisco98

LastCell = Cells(Rows.Count,"A").End(xlUp)

--
HTH

Bob Phillips

"sisco98" wrote in message
...
Hi Everybody,

Does anyone know a way to select the last used cell in a column by using
macro? For example, there is a range filled with data from "A1" to "A30",
but
the closing cell is always changing (A32,A36...). I would like to find the
last data in column "A" by using VLOOKUP automatically.

Thank you in advance for your help,

sisco98

Hi sisco98,

You can try one of these:

1)
Sub Test1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

2)
Sub Test2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub

Regards,
KL


"sisco98" wrote in message
...
Hi Everybody,

Does anyone know a way to select the last used cell in a column by using
macro? For example, there is a range filled with data from "A1" to "A30",
but
the closing cell is always changing (A32,A36...). I would like to find the
last data in column "A" by using VLOOKUP automatically.

Thank you in advance for your help,

sisco98

Use:
range("A65536").End(xlUp).select to select the row
range("A65536").End(xlUp).row for the row number
range("A65536").End(xlUp).address for the cell address

Mangesh



"sisco98" wrote in message
...
Hi Everybody,

Does anyone know a way to select the last used cell in a column by using
macro? For example, there is a range filled with data from "A1" to "A30",
but
the closing cell is always changing (A32,A36...). I would like to find the
last data in column "A" by using VLOOKUP automatically.

Thank you in advance for your help,

sisco98

You will find the last used cell by looking up the first used cell fro
the bottom, so the last used cell in column A would be

myValue = Activesheet.Columns(1).Cells(65536).End(xlUp).Valu e

Regards,
Ton Teun

--
Ton
-----------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=4
View this thread: http://www.officehelp.in/showthread.php?t=65563
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.ph

Hi TonT,

Thank you for your fast help!

Kind regards


--
sisco98


"TonT" wrote:


You will find the last used cell by looking up the first used cell from
the bottom, so the last used cell in column A would be

myValue = Activesheet.Columns(1).Cells(65536).End(xlUp).Valu e

Regards,
Ton Teuns


--
TonT
------------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=47
View this thread: http://www.officehelp.in/showthread.php?t=655633
Visit - http://www.officehelp.in/archive/index.php | http://www.officehelp.in/index/index.php

Hi Mangesh,

thanks for your help!

Regards
--
sisco98


"Mangesh Yadav" wrote:

Use:
range("A65536").End(xlUp).select to select the row
range("A65536").End(xlUp).row for the row number
range("A65536").End(xlUp).address for the cell address

Mangesh



"sisco98" wrote in message
...
Hi Everybody,

Does anyone know a way to select the last used cell in a column by using
macro? For example, there is a range filled with data from "A1" to "A30",
but
the closing cell is always changing (A32,A36...). I would like to find the
last data in column "A" by using VLOOKUP automatically.

Thank you in advance for your help,

sisco98

Hi Bob,

Thank you for your help,

regards
--
sisco98


"Bob Phillips" wrote:


LastCell = Cells(Rows.Count,"A").End(xlUp)

--
HTH

Bob Phillips

"sisco98" wrote in message
...
Hi Everybody,

Does anyone know a way to select the last used cell in a column by using
macro? For example, there is a range filled with data from "A1" to "A30",
but
the closing cell is always changing (A32,A36...). I would like to find the
last data in column "A" by using VLOOKUP automatically.

Thank you in advance for your help,

sisco98

Hello KL,

thanks for quick help!

regards
--
sisco98


"KL" wrote:

Hi sisco98,

You can try one of these:

1)
Sub Test1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

2)
Sub Test2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub

Regards,
KL


"sisco98" wrote in message
...
Hi Everybody,

Does anyone know a way to select the last used cell in a column by using
macro? For example, there is a range filled with data from "A1" to "A30",
but
the closing cell is always changing (A32,A36...). I would like to find the
last data in column "A" by using VLOOKUP automatically.

Thank you in advance for your help,

sisco98

Some of the replies have 65536 as the maximum number of rows in an
Excel worksheet. The number of rows has varied in the past and could
change again. so it would be better to use Rows.Count as in the other
replies. You might find it more useful with a toolbar button see
http://www.mvps.org/dmcritchie/excel...ars.htm#macros
the macro GotoBottomofCurrentColumn and you can use the button icon shown at the
far right. of the macro.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"sisco98" wrote in message ...
Hi Everybody,

Does anyone know a way to select the last used cell in a column by using
macro? For example, there is a range filled with data from "A1" to "A30", but
the closing cell is always changing (A32,A36...). I would like to find the
last data in column "A" by using VLOOKUP automatically.

Thank you in advance for your help,

sisco98