for/next loop
 

I have a sheet in a workbook where I need to select the last row containing
data. Is there a for/next loop function where I can identify the last row,
then select data from it, or can I update two sheets simultaneously with one
input?
I am using Office 97, windows xp.

--

for/next loop
 

sub getlastrow()
lr = Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row

cells(lr,"b").copy somewhere
end sub
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Poor Richard" wrote in message
...
I have a sheet in a workbook where I need to select the last row containing
data. Is there a for/next loop function where I can identify the last row,
then select data from it, or can I update two sheets simultaneously with
one input?
I am using Office 97, windows xp.

--

for/next loop
 

You should specify SearchOrder:=xlRows, otherwise if the last Find a user
did was "By Columns", your code might find the wrong cell (on a blank sheet,
put something in A12 and C10 and run your code after having performed an
Edit/Find using "By Columns"). Also, if the After parameter is not provided,
Find automatically uses the cell at the top right of the search range (which
is A1 for the Cells range). This is what I use...

LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

Of course, ActiveSheet could be left off if the code is to be applied
against the ActiveSheet, but when running from a macro installed in a
Module, that is not always a given. And, of course, ActiveSheet could be
replaced with a direct sheet reference such as Worksheets("Sheet1") if the
search is to be performed against a non-active sheet.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

sub getlastrow()
lr = Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row

cells(lr,"b").copy somewhere
end sub
If desired, send your file to my address below along with this msg
and a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Poor Richard" wrote in message
...
I have a sheet in a workbook where I need to select the last row
containing data. Is there a for/next loop function where I can identify
the last row, then select data from it, or can I update two sheets
simultaneously with one input?
I am using Office 97, windows xp.

--

for/next loop
 

Also, if the After parameter is not provided, Find automatically
uses the cell at the top right of the search range ..

Damn it Rick... read what you write. Of course I meant that Find
automatically uses the cell at the top LEFT of the specified search range.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
You should specify SearchOrder:=xlRows, otherwise if the last Find a user
did was "By Columns", your code might find the wrong cell (on a blank
sheet, put something in A12 and C10 and run your code after having
performed an Edit/Find using "By Columns"). Also, if the After parameter
is not provided, Find automatically uses the cell at the top right of the
search range (which is A1 for the Cells range). This is what I use...

LastUsedRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row

Of course, ActiveSheet could be left off if the code is to be applied
against the ActiveSheet, but when running from a macro installed in a
Module, that is not always a given. And, of course, ActiveSheet could be
replaced with a direct sheet reference such as Worksheets("Sheet1") if the
search is to be performed against a non-active sheet.

--
Rick (MVP - Excel)


"Don Guillett" wrote in message
...

sub getlastrow()
lr = Cells.Find(What:="*", After:=[A1],
SearchDirection:=xlPrevious).Row

cells(lr,"b").copy somewhere
end sub
If desired, send your file to my address below along with this msg
and a clear explanation of what you want and before/after examples.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Poor Richard" wrote in message
...
I have a sheet in a workbook where I need to select the last row
containing data. Is there a for/next loop function where I can identify
the last row, then select data from it, or can I update two sheets
simultaneously with one input?
I am using Office 97, windows xp.

--