Hi,

I didn't try this, but intersecting your "Export" range
with Application.UsedRange should work (although might be
slow if the used range is huge).

Happy programming!

Dag
)


-----Original Message-----
Hello,

I have an excel table that gets refreshed on monthly
basis. I also have set up a macro in Access to append
each new monthly table from excel to an Access table.

The range that I import in Access is named "Export". What
I would like to be able to do is set the "Export" range
to include only rows that are filled with data. The
fields are always the same and the first field is numeric
so I can use the count formula to map the end row. (I am
aware that I can import the whole spreadsheet in access
but the first 6 rows in the excel spreadsheet are not to
be imported.)
Any ideas?
Thank you again for your help.
Diana


.

Thanks for your reponse but it really doesn't answer my
question.

-----Original Message-----
Hi,

I didn't try this, but intersecting your "Export" range
with Application.UsedRange should work (although might
be
slow if the used range is huge).

Happy programming!

Dag
)


-----Original Message-----
Hello,

I have an excel table that gets refreshed on monthly
basis. I also have set up a macro in Access to append
each new monthly table from excel to an Access table.

The range that I import in Access is named "Export".
What
I would like to be able to do is set the "Export" range
to include only rows that are filled with data. The
fields are always the same and the first field is
numeric
so I can use the count formula to map the end row. (I
am
aware that I can import the whole spreadsheet in access
but the first 6 rows in the excel spreadsheet are not
to
be imported.)
Any ideas?
Thank you again for your help.
Diana


.

.

Hi,

if it doesn't answer your question, it could be because I
didn't understand your question. Or it could be that you
are restricted to perform this at the importing side
rather than the exporting. Probably could be many other
things as well. But please give some indication as to what
is wrong with it rather than just state that it's of no
use.

To me it seems what you're saying is that you export a
range from Excel to Access, and you want to restrict that
range to those cells that contain data. If that is indeed
what you are saying then it is unfortunately a little
difficult to see how that can be anything but the
intersection of your range and the "used range", i.e. the
range of non-empty cells.

Please explain in a little more detail what it is you are
trying to achieve.

Sincerely,

Dag
-----Original Message-----
Thanks for your reponse but it really doesn't answer my
question.

-----Original Message-----
Hi,

I didn't try this, but intersecting your "Export" range
with Application.UsedRange should work (although might
be
slow if the used range is huge).

Happy programming!

Dag
)


-----Original Message-----
Hello,

I have an excel table that gets refreshed on monthly
basis. I also have set up a macro in Access to append
each new monthly table from excel to an Access table.

The range that I import in Access is named "Export".
What
I would like to be able to do is set the "Export" range
to include only rows that are filled with data. The
fields are always the same and the first field is
numeric
so I can use the count formula to map the end row. (I
am
aware that I can import the whole spreadsheet in access
but the first 6 rows in the excel spreadsheet are not
to
be imported.)
Any ideas?
Thank you again for your help.
Diana


.

.

.

This should do:

ActiveWorkbook.Names.Add Name:="Exports", RefersToR1C1:= _
"=LOTS!R5C1:R" & n & "C8"

R refers to row number in your case I assume 7
C column number A to N

Y made the n the last rwo on the data.

see if it works.

Andres

-----Original Message-----
I admit that my post is not that easy to understand....

Ok, in my original post I explained that I have no
problem tracking what the last non null row is by using
the count formula. In Access I use a named range
called "Export" to specify the data I want to import.

What I need is a way to dynamically change the cell range
in the Range "Export" by using the number from the count
function. For instance if the count funcion gives me 19
and I know that my fields are always the same (A though
N). Then I want the range "Export" to equal A7:N25 (I
also mentioned that I do not need to export the first 6
lines of the spreadsheet).

Does this make more sense?

-----Original Message-----
Hi,

if it doesn't answer your question, it could be because
I
didn't understand your question. Or it could be that you
are restricted to perform this at the importing side
rather than the exporting. Probably could be many other
things as well. But please give some indication as to
what
is wrong with it rather than just state that it's of no
use.

To me it seems what you're saying is that you export a
range from Excel to Access, and you want to restrict
that
range to those cells that contain data. If that is
indeed
what you are saying then it is unfortunately a little
difficult to see how that can be anything but the
intersection of your range and the "used range", i.e.
the
range of non-empty cells.

Please explain in a little more detail what it is you
are
trying to achieve.

Sincerely,

Dag
-----Original Message-----
Thanks for your reponse but it really doesn't answer my
question.

-----Original Message-----
Hi,

I didn't try this, but intersecting your "Export"
range
with Application.UsedRange should work (although might
be
slow if the used range is huge).

Happy programming!

Dag
)


-----Original Message-----
Hello,

I have an excel table that gets refreshed on monthly
basis. I also have set up a macro in Access to append
each new monthly table from excel to an Access table.

The range that I import in Access is named "Export".
What
I would like to be able to do is set the "Export"
range
to include only rows that are filled with data. The
fields are always the same and the first field is
numeric
so I can use the count formula to map the end row. (I
am
aware that I can import the whole spreadsheet in
access
but the first 6 rows in the excel spreadsheet are not
to
be imported.)
Any ideas?
Thank you again for your help.
Diana


.

.

.

.

.

Yes, this is working out indeed. I subbed n for the
number I get from the count formula plus 6 added to it.
Many Thanks,
Diana

-----Original Message-----

This should do:

ActiveWorkbook.Names.Add Name:="Exports", RefersToR1C1:=
_
"=LOTS!R5C1:R" & n & "C8"

R refers to row number in your case I assume 7
C column number A to N

Y made the n the last rwo on the data.

see if it works.

Andres

-----Original Message-----
I admit that my post is not that easy to understand....

Ok, in my original post I explained that I have no
problem tracking what the last non null row is by using
the count formula. In Access I use a named range
called "Export" to specify the data I want to import.

What I need is a way to dynamically change the cell
range
in the Range "Export" by using the number from the
count
function. For instance if the count funcion gives me 19
and I know that my fields are always the same (A though
N). Then I want the range "Export" to equal A7:N25 (I
also mentioned that I do not need to export the first 6
lines of the spreadsheet).

Does this make more sense?

-----Original Message-----
Hi,

if it doesn't answer your question, it could be
because
I
didn't understand your question. Or it could be that
you
are restricted to perform this at the importing side
rather than the exporting. Probably could be many
other
things as well. But please give some indication as to
what
is wrong with it rather than just state that it's of
no
use.

To me it seems what you're saying is that you export a
range from Excel to Access, and you want to restrict
that
range to those cells that contain data. If that is
indeed
what you are saying then it is unfortunately a little
difficult to see how that can be anything but the
intersection of your range and the "used range", i.e.
the
range of non-empty cells.

Please explain in a little more detail what it is you
are
trying to achieve.

Sincerely,

Dag
-----Original Message-----
Thanks for your reponse but it really doesn't answer
my
question.

-----Original Message-----
Hi,

I didn't try this, but intersecting your "Export"
range
with Application.UsedRange should work (although
might
be
slow if the used range is huge).

Happy programming!

Dag
)


-----Original Message-----
Hello,

I have an excel table that gets refreshed on
monthly
basis. I also have set up a macro in Access to
append
each new monthly table from excel to an Access
table.

The range that I import in Access is
named "Export".
What
I would like to be able to do is set the "Export"
range
to include only rows that are filled with data. The
fields are always the same and the first field is
numeric
so I can use the count formula to map the end row.
(I
am
aware that I can import the whole spreadsheet in
access
but the first 6 rows in the excel spreadsheet are
not
to
be imported.)
Any ideas?
Thank you again for your help.
Diana


.

.

.

.

.

.

R1C8 is cell H1 in A1 notation. How does C8 get you out to column N?

shouldn't it be C14?

Regards,
Tom Ogilvy

andres wrote in message
...

This should do:

ActiveWorkbook.Names.Add Name:="Exports", RefersToR1C1:= _
"=LOTS!R5C1:R" & n & "C8"

R refers to row number in your case I assume 7
C column number A to N

Y made the n the last rwo on the data.

see if it works.

Andres

-----Original Message-----
I admit that my post is not that easy to understand....

Ok, in my original post I explained that I have no
problem tracking what the last non null row is by using
the count formula. In Access I use a named range
called "Export" to specify the data I want to import.

What I need is a way to dynamically change the cell range
in the Range "Export" by using the number from the count
function. For instance if the count funcion gives me 19
and I know that my fields are always the same (A though
N). Then I want the range "Export" to equal A7:N25 (I
also mentioned that I do not need to export the first 6
lines of the spreadsheet).

Does this make more sense?

-----Original Message-----
Hi,

if it doesn't answer your question, it could be because
I
didn't understand your question. Or it could be that you
are restricted to perform this at the importing side
rather than the exporting. Probably could be many other
things as well. But please give some indication as to
what
is wrong with it rather than just state that it's of no
use.

To me it seems what you're saying is that you export a
range from Excel to Access, and you want to restrict
that
range to those cells that contain data. If that is
indeed
what you are saying then it is unfortunately a little
difficult to see how that can be anything but the
intersection of your range and the "used range", i.e.
the
range of non-empty cells.

Please explain in a little more detail what it is you
are
trying to achieve.

Sincerely,

Dag
-----Original Message-----
Thanks for your reponse but it really doesn't answer my
question.

-----Original Message-----
Hi,

I didn't try this, but intersecting your "Export"
range
with Application.UsedRange should work (although might
be
slow if the used range is huge).

Happy programming!

Dag
)


-----Original Message-----
Hello,

I have an excel table that gets refreshed on monthly
basis. I also have set up a macro in Access to append
each new monthly table from excel to an Access table.

The range that I import in Access is named "Export".
What
I would like to be able to do is set the "Export"
range
to include only rows that are filled with data. The
fields are always the same and the first field is
numeric
so I can use the count formula to map the end row. (I
am
aware that I can import the whole spreadsheet in
access
but the first 6 rows in the excel spreadsheet are not
to
be imported.)
Any ideas?
Thank you again for your help.
Diana


.

.

.

.

.