[AccessD] OT: Excel 97 Macro

Dear listers,
unfortunately during the software upgrade one part of software broke. It will be
fixed soon,
but while it is in a we will not mess with anything, that means the confirmation
messages from Oop! software & Computers will keep comming.
thanks for your patience
Lembit Soobik

----- Original Message -----
From: "Bruce Bruen" <bbruen at bigpond.com>
To: <accessd at databaseadvisors.com>
Sent: Monday, March 24, 2003 3:57 AM
Subject: RE: [AccessD] OT: Excel 97 Macro


> Gustav,
>
> Grabbed these off on old excel workbook of mine, cant remember whether
> they worked or whether they are what you're looking for but thought I'd
> send anyway.
>
> Bruce
> <begin>-------
> This workbook contains an example of how to do range lookups
> when the range is not limited e.g. in a database list.
>
>
> It uses two functions LastRow() and ListRange()
>
> Lastrow(sheetname) returns the last row used on a sheet.
> Obviously the list must be contiguous and starting at row one.
>
> Listrange(sheetname, col) returns a range that can be acted on by other
> functions
> such as countif etc
> Since ListRange returns a string representing the range then
> the INDIRECT(Listrange) function is used to address the actual
> range)
>
>
> Public Function LastRow(sheetname As String) As String
>     LastRow = Sheets(sheetname).UsedRange.Rows.Count
> End Function
>
> Public Function ListRange(sheetname As String, col As String)
>     Dim endaddr As String
>     endaddr = LastRow(sheetname)
>     endaddr = col & "1:" & col & endaddr
>     ListRange = Sheets(sheetname).Range(endaddr).Address(External:=True)
> End Function
> <end>---------
>
>
>
> -----Original Message-----
> From: accessd-admin at databaseadvisors.com
> [mailto:accessd-admin at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Sunday, March 23, 2003 4:40 AM
> To: John W. Colby
> Subject: Re: [AccessD] OT: Excel 97 Macro
>
>
> Hi John
>
> > What would be nice is to know the ending row of the data.  Does anyone
>
> > know off the top of their head how to determine the max size (the
> > largest row
> > index) of a populated row?  Remember I am working with what appears to
> be a
> > table - fixed column count / fixed row count.  What is the numbers of
> > records in that table (as determined in Excel).  I can always if
> necessary
> > just link the table in and get a record count but it would be nice to
> figure
> > it out in Excel.
>
> If you have nothing but data in that sheet and these are filled from
> cell A1, then you could use something like this:
>
>   Dim rng As Range
>
>   Set rng = ActiveWorkbook.Worksheets("Data").UsedRange
>   Debug.Print rng.Rows.Count, rng.Columns.Count
>
> /gustav
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> ---
> Incoming mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.445 / Virus Database: 250 - Release Date: 21/01/2003
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.445 / Virus Database: 250 - Release Date: 21/01/2003
>
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>