Select and copy a range of varying size in VBA

rmccafferty · August 5th, 2009, 11:59 PM

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?

Shasur · August 7th, 2009, 10:23 PM

Can you check if the following gets you closer?

Code:

 
Sub Set_New_Range()
Dim oNewRng As Range
Set oNewRng = Range(Cells(2, 1), Cells.SpecialCells(xlCellTypeLastCell))
MsgBox oNewRng.Address
End Sub

Cheers
Shasur