Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find Filled Cell | Excel Discussion (Misc queries) | |||
Macro Request: Fill in empty cells with previous Filled cell in column | Excel Worksheet Functions | |||
Need Formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
go to last filled cell in a column shortcut | Excel Worksheet Functions | |||
How to find last filled in cell in a column? | Excel Worksheet Functions |