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.
|