last entry in column 
Author Message
 last entry in column

Hi Everyone
What I need is just a simple formula to pick up the last entry in a column
or row.
I'm working on a auto mileage chart and I want to export the last entry to
another page, so I can keep up with oil changes.
Also I needed this for a deposit record too!
something perhaps like = (last) or for the mileage chart =(greatest)
I know I made that sound really simple but,only if ya know how, which I
don't.
Thanks for some ideas
first visit and looks like I'll be back

Bill Czerkie



Sat, 25 Oct 2003 11:25:10 GMT  
 last entry in column
Bill, assuming that you have an entry in column A for all rows, you can find
this row by:

dim lastRow as range
set lastRow = cells(65536, 1).end(xlup).EntireRow
'above is the same as going way down the sheet and then pressing end, up
arrow

You can then copy to another sheet:

dim destCell as range
set destCell = sheets("dest sheet").cells(65536,1).end(xlup).Offset(1,0)
lastRow.Copy destCell

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


Quote:
> Hi Everyone
> What I need is just a simple formula to pick up the last entry in a column
> or row.
> I'm working on a auto mileage chart and I want to export the last entry to
> another page, so I can keep up with oil changes.
> Also I needed this for a deposit record too!
> something perhaps like = (last) or for the mileage chart =(greatest)
> I know I made that sound really simple but,only if ya know how, which I
> don't.
> Thanks for some ideas
> first visit and looks like I'll be back

> Bill Czerkie



Sat, 25 Oct 2003 11:44:13 GMT  
 last entry in column
Bob
Let me give another example and a little more info :
lets say in column "F" I keep my mileage list, which in the case of mileage
its ever increasing, but not always so with my deposits.
In "J1 " right now I manually put my last entry from column "F"
then my other sheet picks this up now, and I then know if its time for
maint.
I'm only going to use maybe 100 or so rows for these entries.
Will I need the list below or can it be done with just a formula?
Thanks again And I'll try this tomorrow.
Bill


Quote:
> Bill, assuming that you have an entry in column A for all rows, you can
find
> this row by:

> dim lastRow as range
> set lastRow = cells(65536, 1).end(xlup).EntireRow
> 'above is the same as going way down the sheet and then pressing end, up
> arrow

> You can then copy to another sheet:

> dim destCell as range
> set destCell = sheets("dest sheet").cells(65536,1).end(xlup).Offset(1,0)
> lastRow.Copy destCell

> Bob Flanagan
> Macro Systems
> http://www.add-ins.com
> Productivity add-ins and downloadable books on VB macros for Excel



> > Hi Everyone
> > What I need is just a simple formula to pick up the last entry in a
column
> > or row.
> > I'm working on a auto mileage chart and I want to export the last entry
to
> > another page, so I can keep up with oil changes.
> > Also I needed this for a deposit record too!
> > something perhaps like = (last) or for the mileage chart =(greatest)
> > I know I made that sound really simple but,only if ya know how, which I
> > don't.
> > Thanks for some ideas
> > first visit and looks like I'll be back

> > Bill Czerkie



Sat, 25 Oct 2003 12:09:48 GMT  
 last entry in column
This sounds like a spreadsheet question, rather than a programming one. However,
if you want J1 to aleays pick up the last entry in column F, then you can use the
OFFSET function. eg
in J1 put =OFFSET($F$1,COUNTA(F:F)-1,0)
this counts all the items in column F, then points to the last one ( hence the -1)
It won't work though if there are breaks (gaps) in the data, it must be
contiguous.

Youi can also use a UDF - User Defined Function:

Function LastCellVAlue(cl As String)
    Application.Volatile
    LastCellVAlue = Cells(65536, cl).End(xlUp).Value
End Function

This code will return the value of the last cell used in the selected row,
irrespective of gaps in the list.

HTH
Patrick Molloy
Microsoft Excel MVP

Quote:
-----Original Message-----

Hi Everyone
What I need is just a simple formula to pick up the last entry in a column
or row.
I'm working on a auto mileage chart and I want to export the last entry to
another page, so I can keep up with oil changes.
Also I needed this for a deposit record too!
something perhaps like = (last) or for the mileage chart =(greatest)
I know I made that sound really simple but,only if ya know how, which I
don't.
Thanks for some ideas
first visit and looks like I'll be back

Bill Czerkie

.



Sat, 25 Oct 2003 14:43:45 GMT  
 last entry in column
Bill, I believe someone posted a way to return the last entry in a column
via formula recently.  If you go to http://www.dejanews.com, you will be
redirected to the google newsgroup search engine.  You can then search for
that post.  Perhaps someone reading kept a copy and can repost for you.

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel


Quote:
> Bob
> Let me give another example and a little more info :
> lets say in column "F" I keep my mileage list, which in the case of
mileage
> its ever increasing, but not always so with my deposits.
> In "J1 " right now I manually put my last entry from column "F"
> then my other sheet picks this up now, and I then know if its time for
> maint.
> I'm only going to use maybe 100 or so rows for these entries.
> Will I need the list below or can it be done with just a formula?
> Thanks again And I'll try this tomorrow.
> Bill



> > Bill, assuming that you have an entry in column A for all rows, you can
> find
> > this row by:

> > dim lastRow as range
> > set lastRow = cells(65536, 1).end(xlup).EntireRow
> > 'above is the same as going way down the sheet and then pressing end, up
> > arrow

> > You can then copy to another sheet:

> > dim destCell as range
> > set destCell = sheets("dest sheet").cells(65536,1).end(xlup).Offset(1,0)
> > lastRow.Copy destCell

> > Bob Flanagan
> > Macro Systems
> > http://www.add-ins.com
> > Productivity add-ins and downloadable books on VB macros for Excel



> > > Hi Everyone
> > > What I need is just a simple formula to pick up the last entry in a
> column
> > > or row.
> > > I'm working on a auto mileage chart and I want to export the last
entry
> to
> > > another page, so I can keep up with oil changes.
> > > Also I needed this for a deposit record too!
> > > something perhaps like = (last) or for the mileage chart =(greatest)
> > > I know I made that sound really simple but,only if ya know how, which
I
> > > don't.
> > > Thanks for some ideas
> > > first visit and looks like I'll be back

> > > Bill Czerkie



Sat, 25 Oct 2003 18:42:04 GMT  
 
 [ 5 post ] 

 Relevant Pages 

1. Formula to return last entry in column

2. Pulling LAST entry from column on PREVIOUS Sheet

3. Reference last entry in column

4. Pulling LAST entry from column on PREVIOUS Sheet

5. Have formula utilize last entry in column

6. detecting last entry in columns - for selection

7. fill down column B until I reach the last entry in column A ?

8. Finding the cell under the last entry in a column

9. formula for last entry in a column or row

10. formula for last entry in a column or row


 
Powered by phpBB® Forum Software © phpBB Group