What type of object is objExcel? I would assume it is a worksheet? That being
the case I do not see anything specifically wrong with your code. That
however being said getting the last cell from specialcells can be an issue.
It is not alway what you would think it is. Here is a function that is
guaranteed to get the the true last populated cell...

Public Function LastCell(Optional ByVal wks As Worksheet) As Range
Dim lngLastRow As Long
Dim intLastColumn As Integer

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
intLastColumn = wks.Cells.Find(What:="*", _
After:=wks.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
intLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, intLastColumn)
End Function

You could use this function something like this...

With objExcel
.range(.range("A1"), LastCell(objExcel)).Sort Key1:="Date",
Header:=xlYes
End With

--
HTH...

Jim Thomlinson


"Ian" wrote:

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