Define a Range depending on where the EOF is

[Dag Johansen]

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
([email protected])

 

[Diana]

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

 

[Dag Johansen]

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

 

[andres]

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

 

[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

 

[Tom Ogilvy]

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

shouldn't it be C14?

Regards,
Tom Ogilvy