finding the last row populated in an excel object

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a VB application that I am using to create a series of excel reports. I am using excel's copy from recordset to transfer an adodb.recordset to my excel object starting in cell A4. The problem I need to resolve is I need to find the highest row populated in the excel object. For whatever reason I am unable to access the adodb.recordset's .recordcount
property. I need to add text at the bottom of the excel so I want to be able to see that the highest populated row is 255 now I can assign a text value to cell A256. How do I identify the maximum row populated in column a considering that data in column A will not begin untill cell A4. Any help will be greatly appreciated. Thanks in advance.
 
eli,

range("a65536").End(xlUp).Row
Will give you the row number of the very last row with data in column "A".

range("a4").End(xlDown).Row
Will give you the last row that's not blank starting at "A4"

Just add 1 to either of the above.

John

eli silverman said:
I have a VB application that I am using to create a series of excel
Click to expand...
reports. I am using excel's copy from recordset to transfer an
adodb.recordset to my excel object starting in cell A4. The problem I need
to resolve is I need to find the highest row populated in the excel object.
For whatever reason I am unable to access the adodb.recordset's .recordcount
property. I need to add text at the bottom of the excel so I want to be
Click to expand...
able to see that the highest populated row is 255 now I can assign a text
value to cell A256. How do I identify the maximum row populated in column a
considering that data in column A will not begin untill cell A4. Any help
will be greatly appreciated. Thanks in advance.
 
John,

It's my mission to remove code such as Range("A65536")<VBG>

It should be (IMO)

Range("A"&Rows.Count)

it may vary one day, it certainly hasn't always been 65536.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
It's my mission to remove code such as Range("A65536")
Click to expand...
One convert at a time????
Okay, it makes sense, so you've got my vote.
Now all I have to do is remember to use it.
It's difficult to break old habits, but I'll try.

John
 
Good man. 1 down, 5,732 to go (or is that 65535?).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Eli -

You may be able to access the ADO recordset recordcount by
using the client as the cursor location. Before opening
your existing recordset, insert the following:

rst.CursorLocation = adUseClient
rst.Open ...

This method may be slower than using the default server-
side cursor but should allow access to the recordcount.

I would suggest using both client-side cursor recordcount
and Range("A4").End(xlDown).Row (or the method supplied by
Bob Phillips) to ensure that all the rows were copied from
the recordset.

Eric Scott
 
The ability to use the RecordCount property is dependent on the cursor
type and the provider.

--
 
Back
Top