Macro to find end of rows and print message

  • Thread starter Thread starter DumbCluck
  • Start date Start date
D

DumbCluck

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??
 
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
 
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!
 
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
 
Back
Top