VBA in Excel Macro - Counting Rows w/in Sheet 1

[pkirkley]

I need to count the number of records that my sheet has in it and then populate a trailer record after the last valid record in the sheet. The issue I have is that the sheet is in a workbook that contains many sheets. I can perform a DO loop looking for a ISNULL condition to accumulate my row count but it appears that I have to reference the sheet by name or number and the macro has to be generic enough that it can be performed on any active sheet within the workbook. I was thinking that there might be some Domain Object within Excel to get the active sheets name but I can not find a reference in the help files to do that. Any suggestions from the forum?

 

[kylua]

Not quite sure what your after but:

activesheet.name returns the name or you can just refer to the activesheet.cells(x,y) or .range(address)

 

[pkirkley]

That is a part of what I am trying to do. Thanks!

 

[acron]

How about

Dim lngRecords As Long
lngRecords = ActiveSheet.UsedRange.Rows.Count

which returns the number of rows used in the sheet.

you might then use [A1].Offset(lngRecords,0) to get the first cell in the first blank row.

All this assumes that your sheet has no blank data.

A.C.