[AccessD] OT: Excel 97 Macro

This construct works even if there is no data in A1.

Sub TestUsedRange()
Dim oUsed As Range, lLastRow As Long, lLastCol As Long
Set oUsed = ThisWorkbook.Sheets(1).UsedRange
lLastRow = oUsed.Cells.SpecialCells(xlCellTypeLastCell).Row
lLastCol = oUsed.Cells.SpecialCells(xlCellTypeLastCell).Column
Debug.Print lLastRow, lLastCol
End Sub

Steve Goodhall

-----Original Message-----
From: accessd-admin at databaseadvisors.com
[mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Saturday, March 22, 2003 12:40 PM
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