Select a range of varying size in Excel with VBA

[rjmccafferty1]

I want to have Access import one worksheet from a workbook with an Access macro. Access macros apparently will not allow you to select a specific worksheet, but will allow you to select a range.

Therefor I need to select a range and assign it a range name in Excel so that Access can then import it.

The complication is that the number of rows will change every time this event occurs and the same range name must be used every time.

To add to the issue, the first row has column headers and must not be included in the selected range.

If I were doing this with the keyboard, I would move to cell A2 and then hold down Shift-Control-End and then assign a name to the selected cells.

Anyone know how to do this in code?

 

[xlbo]

there are at least 2 FAQs in this forum for finding the last row of data

Have a look at those, then record yourself defining a named range

Take your code and where you have something like

"$A$2:$Z$1000"

replace it with

"$A$2:$Z$" & lRow

where lRow is your placeholder for the last row of data found in your worksheet

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question