Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to find end of rows and print message
I have this recorded macro:
Sub Macro2() Selection.SpecialCells(xlCellTypeLastCell).Select Range("I41").Select Selection.End(xlToLeft).Select ActiveCell.FormulaR1C1 = "this is a test" Range("B41").Select End Sub How can I get it to go to the bottom of the data regardless of the number of rows? Ain't I a dummy?? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to find end of rows and print message
DumbCluck,
First, no you are not a dummy, you just don't know. Hopefully we can teach you. Second, what do you mean by "How can I get it to go to the bottom of the data regardless of the number of rows?"? After it is done running, do you want the active/selected cell to be in the last row of the data? Do you want some message or number to be entered in one of the cells in the last row? Also, the last row of your data and the last row of what XL thinks is the "Used Range" might be 2 different rows. Your code doesn't do anything.......it just moves around to different cells and puts "this is a test" into a cell before column I in row 41. What would you like it to do? If you want the active/selected cell to be in the last row of what XL thinks is the used range, then move the line "Selection.SpecialCells(xlCellTypeLastCell).Select " to the end (you could have it replace "Range("B41").Select". Now if you want the active/selected cell to be in the last row of your data, and the last row of your data and the last row of the used range are different, then that is slightly more complicated. I'll need to get some more info from you. Do you want it to be any cell in the last row or a specific cell in the last row? Also, we will need to use a column that has contents in every row of your data in order to find the last row of your data. HTH, Conan "DumbCluck" wrote in message ... I have this recorded macro: Sub Macro2() Selection.SpecialCells(xlCellTypeLastCell).Select Range("I41").Select Selection.End(xlToLeft).Select ActiveCell.FormulaR1C1 = "this is a test" Range("B41").Select End Sub How can I get it to go to the bottom of the data regardless of the number of rows? Ain't I a dummy?? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to find end of rows and print message
Thanks Conan,
The spreadsheets are variable in the number of rows, some may have 100 rows, some only 2. I have 4 lines of text to enter at the end of the spreadsheet. (also would like a space between the data and the text. Your suggestion works find, but now I need to drop down a line and have another line of text entered. thanks for your help....I appreciate it! "Conan Kelly" wrote: DumbCluck, First, no you are not a dummy, you just don't know. Hopefully we can teach you. Second, what do you mean by "How can I get it to go to the bottom of the data regardless of the number of rows?"? After it is done running, do you want the active/selected cell to be in the last row of the data? Do you want some message or number to be entered in one of the cells in the last row? Also, the last row of your data and the last row of what XL thinks is the "Used Range" might be 2 different rows. Your code doesn't do anything.......it just moves around to different cells and puts "this is a test" into a cell before column I in row 41. What would you like it to do? If you want the active/selected cell to be in the last row of what XL thinks is the used range, then move the line "Selection.SpecialCells(xlCellTypeLastCell).Select " to the end (you could have it replace "Range("B41").Select". Now if you want the active/selected cell to be in the last row of your data, and the last row of your data and the last row of the used range are different, then that is slightly more complicated. I'll need to get some more info from you. Do you want it to be any cell in the last row or a specific cell in the last row? Also, we will need to use a column that has contents in every row of your data in order to find the last row of your data. HTH, Conan "DumbCluck" wrote in message ... I have this recorded macro: Sub Macro2() Selection.SpecialCells(xlCellTypeLastCell).Select Range("I41").Select Selection.End(xlToLeft).Select ActiveCell.FormulaR1C1 = "this is a test" Range("B41").Select End Sub How can I get it to go to the bottom of the data regardless of the number of rows? Ain't I a dummy?? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to find end of rows and print message
DumbCluck,
I would do something like this: Sub Macro2() dim prngLastCell as range set prngLastCell = Selection.SpecialCells(xlCellTypeLastCell) Range("I41").Select Selection.End(xlToLeft).Select ActiveCell.FormulaR1C1 = "this is a test" cells(prngLastCell.row + 1, prngLastCell.column).select ActiveCell = "Enter your text here" End Sub This will put "Enter your text here" in the cell directly below the cell that XL thinks is the last cell (could be diffent from the last cell of your data). If you want a blank line between the last cell and the text, change "cells(prngLastCell.row + 1, prngLastCell.column).select" to "cells(prngLastCell.row + 2, prngLastCell.column).select" HTH, Conan "DumbCluck" wrote in message ... Thanks Conan, The spreadsheets are variable in the number of rows, some may have 100 rows, some only 2. I have 4 lines of text to enter at the end of the spreadsheet. (also would like a space between the data and the text. Your suggestion works find, but now I need to drop down a line and have another line of text entered. thanks for your help....I appreciate it! "Conan Kelly" wrote: DumbCluck, First, no you are not a dummy, you just don't know. Hopefully we can teach you. Second, what do you mean by "How can I get it to go to the bottom of the data regardless of the number of rows?"? After it is done running, do you want the active/selected cell to be in the last row of the data? Do you want some message or number to be entered in one of the cells in the last row? Also, the last row of your data and the last row of what XL thinks is the "Used Range" might be 2 different rows. Your code doesn't do anything.......it just moves around to different cells and puts "this is a test" into a cell before column I in row 41. What would you like it to do? If you want the active/selected cell to be in the last row of what XL thinks is the used range, then move the line "Selection.SpecialCells(xlCellTypeLastCell).Select " to the end (you could have it replace "Range("B41").Select". Now if you want the active/selected cell to be in the last row of your data, and the last row of your data and the last row of the used range are different, then that is slightly more complicated. I'll need to get some more info from you. Do you want it to be any cell in the last row or a specific cell in the last row? Also, we will need to use a column that has contents in every row of your data in order to find the last row of your data. HTH, Conan "DumbCluck" wrote in message ... I have this recorded macro: Sub Macro2() Selection.SpecialCells(xlCellTypeLastCell).Select Range("I41").Select Selection.End(xlToLeft).Select ActiveCell.FormulaR1C1 = "this is a test" Range("B41").Select End Sub How can I get it to go to the bottom of the data regardless of the number of rows? Ain't I a dummy?? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to find and paste rows | Excel Discussion (Misc queries) | |||
Macro to find and delete rows! | Excel Discussion (Misc queries) | |||
Macro to: Find a Reference, and then Paste into the 10 Rows Below | Excel Discussion (Misc queries) | |||
Macro - message box if find nothing | Excel Discussion (Misc queries) | |||
macro to not print blank value rows in invoice | Excel Worksheet Functions |