Hi Rick

The code is running under Access VBA. The Excel application is defined as
objExcel and, as you suggested there is a preceding With statement.

The problem I have with your suggested code the first line. Under Access I
can not dimension as a range.

I've managed to get it working with:

Dim objRange as Object
Set objRange = objExcel.range(.Cells(3, lngMaxColum), .Cells(lngMaxRow,
lngMaxColumn))

Thanks for the pointer, I hadn't thought to use Set.

--
Ian
--
"Rick Rothstein (MVP - VB)" wrote in
message ...
What is the 'dot' doing in front of the keyword Cells? Do you have a With
statement somewhere?

Anyway, to see that you can create a range using Resize, copy/paste and
then run the following code...

Sub Test()
Dim R As Range
Set R = Cells(1, 1).Resize(10, 10)
R.Value = "I'm yellow."
R.Interior.Color = vbYellow
End Sub

It will fill the first 10 columns and rows with the words "I'm yellow" and
make the cell's interiors yellow.

Rick



"Ian" wrote in message
...
Hi Rick

Is it possible to create a range variable from Resize?

strRange = .Cells(1,1).Resize(lngMaxRow, lngMaxColumn) gives Type
mismatch.

Alternatively, how could I achieve this using Cells, rather than Range

strRange = .Range(.Cells(1, 1), .Cells(lngMaxRow, lngMaxColumn)) also
gives Type mismatch


"Rick Rothstein (MVP - VB)" wrote in
message ...
You can use Resize to create the range you want. I would think this will
do what you want...

objExcel.Range("A1").Resize(lngMaxRow, 14).Sort Key1:="Date",
Header:=xlYes

You could use Cells(1, 1) in place of the Range("A1")... this would
allow you to use variables in order to specify the initial cell from
which you perform the resize instead.

Rick


"Ian" wrote in message
...
I have this line of code and I want to remove the hard-coded "N" and
replace it with the last column (lngMaxColumn retruned by SpecialCells).

objExcel.range("A1:N" & lngMaxRow).Sort Key1:="Date", Header:=xlYes

I have a routine which turns the column number into a letter, but I'd
rather use the column number if possible. I know I can change
Range("N1") to Cells(14,1), but how do you specify a range of cells in
this way?

I've tried

With objExcel
.range(.cells(1, 1), .cells(lngMaxRow, lngMaxColumn)).Sort
Key1:="Date", Header:=xlYes
End With

but this returns "Run-time error '1004': Application defined or object
defined error" and appears to be to do with the .cells references.

Excel VBA Help gives Range(Cells(1, 1), Cells(5, 3)).Font.Italic = True
as an example, but I can't figure out how to adapt this to late-bound
code. It needs to be late-bound as it's running under Access.

Any ideas?

Ian