Find last filled cell in a column
 

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a column?

For exmaple I have col A and in that column first five cells filled up and
then next three cells are empty and then again next five are filled up. I
need to find the last cell in the column which is filled up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell in another column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a column?

For exmaple I have col A and in that column first five cells filled up and
then next three cells are empty and then again next five are filled up. I
need to find the last cell in the column which is filled up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is column A as an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell in another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a column?

For exmaple I have col A and in that column first five cells filled up
and
then next three cells are empty and then again next five are filled
up. I
need to find the last cell in the column which is filled up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

tom,
thanks for your reply. how would i implement this code into the sheet. where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is column A as an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell in another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a column?

For exmaple I have col A and in that column first five cells filled up
and
then next three cells are empty and then again next five are filled
up. I
need to find the last cell in the column which is filled up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu there, do
Insert=Module
Paste the code in the module that is inserted. Change the code to reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code into the sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is column A as an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell in another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a column?

For exmaple I have col A and in that column first five cells
filled up
and
then next three cells are empty and then again next five are
filled
up. I
need to find the last cell in the column which is filled up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

Tom,

thanks once again. your help is highly appreciated. just 1 last request.

in the code you gave me i have to specify which cell to put the results
into. can we make the code such that it puts the address of the cell that is
selected in the spreadsheet automatically? rather than me changing the code
everytime.

or....is it possible that excel asks me to which cell i want to put the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu there, do
Insert=Module
Paste the code in the module that is inserted. Change the code to reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code into the sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is column A as an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell in another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a column?

For exmaple I have col A and in that column first five cells
filled up
and
then next three cells are empty and then again next five are
filled
up. I
need to find the last cell in the column which is filled up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
Tom,

thanks once again. your help is highly appreciated. just 1 last request.

in the code you gave me i have to specify which cell to put the results
into. can we make the code such that it puts the address of the cell that
is
selected in the spreadsheet automatically? rather than me changing the
code
everytime.

or....is it possible that excel asks me to which cell i want to put the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu there, do
Insert=Module
Paste the code in the module that is inserted. Change the code to
reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code into the sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is column A as
an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell in
another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a
column?

For exmaple I have col A and in that column first five cells
filled up
and
then next three cells are empty and then again next five are
filled
up. I
need to find the last cell in the column which is filled up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

1) instead of putting the value of the cell, can we put its cell address. so
that if the contents change, it will be updated in the target cell.

2) can we also do the same thing you did to the current cell to the current
worksheet?

thanks
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
Tom,

thanks once again. your help is highly appreciated. just 1 last request.

in the code you gave me i have to specify which cell to put the results
into. can we make the code such that it puts the address of the cell that
is
selected in the spreadsheet automatically? rather than me changing the
code
everytime.

or....is it possible that excel asks me to which cell i want to put the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu there, do
Insert=Module
Paste the code in the module that is inserted. Change the code to
reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code into the sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is column A as
an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell in
another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a
column?

For exmaple I have col A and in that column first five cells
filled up
and
then next three cells are empty and then again next five are
filled
up. I
need to find the last cell in the column which is filled up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell.Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy


"Hesham" wrote in message
...
1) instead of putting the value of the cell, can we put its cell address.
so
that if the contents change, it will be updated in the target cell.

2) can we also do the same thing you did to the current cell to the
current
worksheet?

thanks
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
Tom,

thanks once again. your help is highly appreciated. just 1 last
request.

in the code you gave me i have to specify which cell to put the
results
into. can we make the code such that it puts the address of the cell
that
is
selected in the spreadsheet automatically? rather than me changing the
code
everytime.

or....is it possible that excel asks me to which cell i want to put
the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu there, do
Insert=Module
Paste the code in the module that is inserted. Change the code to
reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code into the
sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is column A
as
an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell in
another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a
column?

For exmaple I have col A and in that column first five
cells
filled up
and
then next three cells are empty and then again next five
are
filled
up. I
need to find the last cell in the column which is filled
up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

tom
i want to take the value that we found (the last cell in column1) and insert
it after the last filled cell in a different column in a different sheet.

can you help me with that.

Thanks alot.

Hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell.Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy


"Hesham" wrote in message
...
1) instead of putting the value of the cell, can we put its cell address.
so
that if the contents change, it will be updated in the target cell.

2) can we also do the same thing you did to the current cell to the
current
worksheet?

thanks
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
Tom,

thanks once again. your help is highly appreciated. just 1 last
request.

in the code you gave me i have to specify which cell to put the
results
into. can we make the code such that it puts the address of the cell
that
is
selected in the spreadsheet automatically? rather than me changing the
code
everytime.

or....is it possible that excel asks me to which cell i want to put
the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu there, do
Insert=Module
Paste the code in the module that is inserted. Change the code to
reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code into the
sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is column A
as
an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell in
another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell in a
column?

For exmaple I have col A and in that column first five
cells
filled up
and
then next three cells are empty and then again next five
are
filled
up. I
need to find the last cell in the column which is filled
up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

For a value

You asked for the value this time, so for a value:

Sub PopCell1()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Value = rgLastCell.Value
End Sub

for a formula

Sub PopCell2()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom
i want to take the value that we found (the last cell in column1) and
insert
it after the last filled cell in a different column in a different sheet.

can you help me with that.

Thanks alot.

Hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell.Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy


"Hesham" wrote in message
...
1) instead of putting the value of the cell, can we put its cell
address.
so
that if the contents change, it will be updated in the target cell.

2) can we also do the same thing you did to the current cell to the
current
worksheet?

thanks
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
Tom,

thanks once again. your help is highly appreciated. just 1 last
request.

in the code you gave me i have to specify which cell to put the
results
into. can we make the code such that it puts the address of the
cell
that
is
selected in the spreadsheet automatically? rather than me changing
the
code
everytime.

or....is it possible that excel asks me to which cell i want to
put
the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu there,
do
Insert=Module
Paste the code in the module that is inserted. Change the code
to
reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code into
the
sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is
column A
as
an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell
in
another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell
in a
column?

For exmaple I have col A and in that column first five
cells
filled up
and
then next three cells are empty and then again next
five
are
filled
up. I
need to find the last cell in the column which is
filled
up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

tom,
your help is very very highly appreciated. things are alot easier now.

i have a little bit more challenging (i think) request. Say that i have a
table in a spreadsheet. i want as soon as i open the file, a message box pops
up, asks me to input a value, which is then recorded after the last filled
cell in a column in that table.

if that is doable i would appreciate your help with it.

thanks

hesham
"Tom Ogilvy" wrote:

For a value

You asked for the value this time, so for a value:

Sub PopCell1()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Value = rgLastCell.Value
End Sub

for a formula

Sub PopCell2()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom
i want to take the value that we found (the last cell in column1) and
insert
it after the last filled cell in a different column in a different sheet.

can you help me with that.

Thanks alot.

Hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell.Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy


"Hesham" wrote in message
...
1) instead of putting the value of the cell, can we put its cell
address.
so
that if the contents change, it will be updated in the target cell.

2) can we also do the same thing you did to the current cell to the
current
worksheet?

thanks
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
Tom,

thanks once again. your help is highly appreciated. just 1 last
request.

in the code you gave me i have to specify which cell to put the
results
into. can we make the code such that it puts the address of the
cell
that
is
selected in the spreadsheet automatically? rather than me changing
the
code
everytime.

or....is it possible that excel asks me to which cell i want to
put
the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu there,
do
Insert=Module
Paste the code in the module that is inserted. Change the code
to
reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code into
the
sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is
column A
as
an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in message
...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled cell
in
another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled cell
in a
column?

For exmaple I have col A and in that column first five
cells
filled up
and
then next three cells are empty and then again next
five
are
filled
up. I
need to find the last cell in the column which is
filled
up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm

You would want to use the workbook_open event in the Thisworkbook Module

In the vbe, go to the project explorer and in the listing for your workbook,
double click on the thisworkbook entry.

this should open a module with two dropdown boxes at the top. In the left
dropdown, select workbook and in the right dropdown select Open.

this will put in the declaration

Private Sub Workbook_Open()

end Sub

You would put your code here

Private Sub Workbook_Open()
Dim sh as Worksheet
Dim ans as Variant
Dim rgLastCell as range
Thisworkbook.Worksheets(1).Activate
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
ans = InputBox("Please enter the desired Value")
If isnumeric(ans) then
rngLastCell.Offset(1,0).Value = cdbl(ans)
End If
End Sub

--
Regards,
Tom Ogilvy


"Hesham" wrote in message
...
tom,
your help is very very highly appreciated. things are alot easier now.

i have a little bit more challenging (i think) request. Say that i have a
table in a spreadsheet. i want as soon as i open the file, a message box
pops
up, asks me to input a value, which is then recorded after the last filled
cell in a column in that table.

if that is doable i would appreciate your help with it.

thanks

hesham
"Tom Ogilvy" wrote:

For a value

You asked for the value this time, so for a value:

Sub PopCell1()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Value = rgLastCell.Value
End Sub

for a formula

Sub PopCell2()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom
i want to take the value that we found (the last cell in column1) and
insert
it after the last filled cell in a different column in a different
sheet.

can you help me with that.

Thanks alot.

Hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell.Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy


"Hesham" wrote in message
...
1) instead of putting the value of the cell, can we put its cell
address.
so
that if the contents change, it will be updated in the target
cell.

2) can we also do the same thing you did to the current cell to
the
current
worksheet?

thanks
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
Tom,

thanks once again. your help is highly appreciated. just 1
last
request.

in the code you gave me i have to specify which cell to put
the
results
into. can we make the code such that it puts the address of
the
cell
that
is
selected in the spreadsheet automatically? rather than me
changing
the
code
everytime.

or....is it possible that excel asks me to which cell i want
to
put
the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu
there,
do
Insert=Module
Paste the code in the module that is inserted. Change the
code
to
reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code
into
the
sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is
column A
as
an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in
message

...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled
cell
in
another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled
cell
in a
column?

For exmaple I have col A and in that column first
five
cells
filled up
and
then next three cells are empty and then again
next
five
are
filled
up. I
need to find the last cell in the column which is
filled
up.

Help would be appreciated.

Thanks in advance.

Find last filled cell in a column
 

regarding your last macro, say that i want to put a restriction that the
macro only runs automatically everytime the file is opened during the first
week of the each month. on any other day it will open normally (without the
macro).
=============================================
is it possible to setup a macro for microsoft word that adds 1 to a number
that is in a field, everytime this file is opened?!. eg. we have a reference
code 0506, the next time this file opens becomes 0507 and so on.
==============================================
just out of curiosity, is excel capable of opening a website, extract
information from it, and add it to a spreadsheet? or you think this is too
complicated.

thanks alot tom.

regards

"Tom Ogilvy" wrote:

See Chip Pearson's page on Events
http://www.cpearson.com/excel/events.htm

You would want to use the workbook_open event in the Thisworkbook Module

In the vbe, go to the project explorer and in the listing for your workbook,
double click on the thisworkbook entry.

this should open a module with two dropdown boxes at the top. In the left
dropdown, select workbook and in the right dropdown select Open.

this will put in the declaration

Private Sub Workbook_Open()

end Sub

You would put your code here

Private Sub Workbook_Open()
Dim sh as Worksheet
Dim ans as Variant
Dim rgLastCell as range
Thisworkbook.Worksheets(1).Activate
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
ans = InputBox("Please enter the desired Value")
If isnumeric(ans) then
rngLastCell.Offset(1,0).Value = cdbl(ans)
End If
End Sub

--
Regards,
Tom Ogilvy


"Hesham" wrote in message
...
tom,
your help is very very highly appreciated. things are alot easier now.

i have a little bit more challenging (i think) request. Say that i have a
table in a spreadsheet. i want as soon as i open the file, a message box
pops
up, asks me to input a value, which is then recorded after the last filled
cell in a column in that table.

if that is doable i would appreciate your help with it.

thanks

hesham
"Tom Ogilvy" wrote:

For a value

You asked for the value this time, so for a value:

Sub PopCell1()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Value = rgLastCell.Value
End Sub

for a formula

Sub PopCell2()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.cells(rows.count,"A").End(xlup)
sh.Cells(rows.count,"F").End( _
xlup)(2).Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom
i want to take the value that we found (the last cell in column1) and
insert
it after the last filled cell in a different column in a different
sheet.

can you help me with that.

Thanks alot.

Hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = ActiveSheet
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell.Formula = "=" & rgLastCell.Address
End Sub

--
Regards,
Tom Ogilvy


"Hesham" wrote in message
...
1) instead of putting the value of the cell, can we put its cell
address.
so
that if the contents change, it will be updated in the target
cell.

2) can we also do the same thing you did to the current cell to
the
current
worksheet?

thanks
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
ActiveCell = rgLastCell.Value
End Sub

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
Tom,

thanks once again. your help is highly appreciated. just 1
last
request.

in the code you gave me i have to specify which cell to put
the
results
into. can we make the code such that it puts the address of
the
cell
that
is
selected in the spreadsheet automatically? rather than me
changing
the
code
everytime.

or....is it possible that excel asks me to which cell i want
to
put
the
results. popup msg or so.

thanks alot.

regards,

hesham
"Tom Ogilvy" wrote:

Sub PopCell()
Dim sh as Worksheet
Dim rgLastCell as range
set Sh = worksheets("Sheet1")
Set rgLastCell=sh.range("A65536").End(xlup)
sh.Range("G9").Value = rgLastCell.Value
End Sub

to Alt+F11 to go to the Visual Basic Editor. In the menu
there,
do
Insert=Module
Paste the code in the module that is inserted. Change the
code
to
reflect
the sheet, cell and column as appropriate

do Alt+F11 to get back to excel.


When you want to run the macro, do
Tools=Macro=Macros, select PopCell and click Run

--
Regards,
Tom Ogilvy



"Hesham" wrote in message
...
tom,
thanks for your reply. how would i implement this code
into
the
sheet.
where
do i actually write it?

thanks

"Tom Ogilvy" wrote:

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)
Range("G9").Value = rgLastCell.Value

if the objective cell ig G9 and the "another" column is
column A
as
an
example.

--
Regards,
Tom Ogilvy

"Hesham" wrote in
message

...
Hi

how would i use your code iny my spreadsheet?!
my objective is filling a cell with the last filled
cell
in
another
column.

thanks

"sebastienm" wrote:

Hi VJ,

Dim rgLastCell as range
Set rgLastCell=Activesheet.range("A65536").End(xlup)

Regards,
Sébastien

"VJ" wrote:

Hi,

Is there any way I can find out the last filled
cell
in a
column?

For exmaple I have col A and in that column first
five
cells
filled up
and
then next three cells are empty and then again
next
five
are
filled
up. I
need to find the last cell in the column which is
filled
up.

Help would be appreciated.

Thanks in advance.