[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