I am working on an applicatin in Excel with a Userform where the user selects
which SQL queries to run.
With each SQL query, I am writting to a sheet the result set(s) top to
bottom. (All result set(s) are written to the same sheet (DataResults)).
Since the result sets are dynamic, I need to know where I am on the sheet
(row position after writting the result set to the sheet. With each result
set, I am also naming the range.
However, because I can't rely on each result set (range) being executed
(based on userform), I can't simply go to name range and use row.count with
starting row number to determine where the next range should start (for
writting).

I could keep a running variable going by addiing up all lines written, but
there must be some Excel property for the sheet that I can use. I can't seem
to find that, so I'm asking here.
I tried the row.count for the sheet, but that gave the whole sheet row
count, not the last line "used".
This DataResults sheet will then be hidden and used to make charts and
reports on other sheets using the named ranges written on this sheet.

Thanks

--
ptnagle

lastrow = Worksheets("DataResults").Cells(rows.count,1).end( xlup).row

--
Regards,
Tom Ogilvy


"ptnagle" wrote in message
...
I am working on an applicatin in Excel with a Userform where the user
selects
which SQL queries to run.
With each SQL query, I am writting to a sheet the result set(s) top to
bottom. (All result set(s) are written to the same sheet (DataResults)).
Since the result sets are dynamic, I need to know where I am on the sheet
(row position after writting the result set to the sheet. With each result
set, I am also naming the range.
However, because I can't rely on each result set (range) being executed
(based on userform), I can't simply go to name range and use row.count
with
starting row number to determine where the next range should start (for
writting).

I could keep a running variable going by addiing up all lines written, but
there must be some Excel property for the sheet that I can use. I can't
seem
to find that, so I'm asking here.
I tried the row.count for the sheet, but that gave the whole sheet row
count, not the last line "used".
This DataResults sheet will then be hidden and used to make charts and
reports on other sheets using the named ranges written on this sheet.

Thanks

--
ptnagle

Tom,

Thanks for the tip - it worked as I wished.

However, I am now trying to use the .CopyFromRecordset method to add the
contents of rs to the sheet.
I can't get it to work unless I use Range("A7").CopyFromRecordset rs
I need to change "A7" to the next row,1 from the earlier statement you
provided.
I don't understand why Range(7,1) doesn't work.

Thanks

"ptnagle" wrote:

I am working on an applicatin in Excel with a Userform where the user selects
which SQL queries to run.
With each SQL query, I am writting to a sheet the result set(s) top to
bottom. (All result set(s) are written to the same sheet (DataResults)).
Since the result sets are dynamic, I need to know where I am on the sheet
(row position after writting the result set to the sheet. With each result
set, I am also naming the range.
However, because I can't rely on each result set (range) being executed
(based on userform), I can't simply go to name range and use row.count with
starting row number to determine where the next range should start (for
writting).

I could keep a running variable going by addiing up all lines written, but
there must be some Excel property for the sheet that I can use. I can't seem
to find that, so I'm asking here.
I tried the row.count for the sheet, but that gave the whole sheet row
count, not the last line "used".
This DataResults sheet will then be hidden and used to make charts and
reports on other sheets using the named ranges written on this sheet.

Thanks

--
ptnagle

Cells(lastrow,1).CopyFromRecordSet

Range doesn't support a row, column argument.

Cells does.

--
Regards,
Tom Ogilvy


"ptnagle" wrote in message
...
Tom,

Thanks for the tip - it worked as I wished.

However, I am now trying to use the .CopyFromRecordset method to add the
contents of rs to the sheet.
I can't get it to work unless I use Range("A7").CopyFromRecordset rs
I need to change "A7" to the next row,1 from the earlier statement you
provided.
I don't understand why Range(7,1) doesn't work.

Thanks

"ptnagle" wrote:

I am working on an applicatin in Excel with a Userform where the user
selects
which SQL queries to run.
With each SQL query, I am writting to a sheet the result set(s) top to
bottom. (All result set(s) are written to the same sheet (DataResults)).
Since the result sets are dynamic, I need to know where I am on the
sheet
(row position after writting the result set to the sheet. With each
result
set, I am also naming the range.
However, because I can't rely on each result set (range) being executed
(based on userform), I can't simply go to name range and use row.count
with
starting row number to determine where the next range should start (for
writting).

I could keep a running variable going by addiing up all lines written,
but
there must be some Excel property for the sheet that I can use. I can't
seem
to find that, so I'm asking here.
I tried the row.count for the sheet, but that gave the whole sheet row
count, not the last line "used".
This DataResults sheet will then be hidden and used to make charts and
reports on other sheets using the named ranges written on this sheet.

Thanks

--
ptnagle