for/next loop

  • Thread starter Thread starter Poor Richard
  • Start date Start date
P

Poor Richard

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.

--
 
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.
 
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 said:
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.
Click to expand...
 
Also, if the After parameter is not provided, Find automatically
uses the cell at the top right of the search range ..
Click to expand...

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 said:
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 said:
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
(e-mail address removed)
Poor Richard said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top