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

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

Hi Jim

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
Yes, it's a worksheet.
however being said getting the last cell from specialcells can be an
issue.

I realise Excel sometimes lies about what the last occupied cell is,
particularly if data has been entered, then deleted. In this case, it's not
an issue as the spreadsheet is created from scratch by output from Access.

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...

I'll file your routine away for future use. Thank you.

Ian

On 23 Nov., 18:38, "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

Hi Ian

Try to remove the dots before "Cells..."

With objExcel
.Range(Cells(1, 1), Cells(lngMaxRow, lngMaxColumn)).Sort
Key1:="Date", _
Header:=xlYes
End With

Regards,

Per

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.


Try to remove the dots before "Cells..."
I get "Sub or Function not defined" as Access doesn't understand "Cells"

Ian

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

Boy, do I feel stupid!!!

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

This works perfectly well until I substitute 14 with lngMaxColumn. Major
hint here, I think.

I am working with data exported to several spreadsheets from Access and
different subroutines work on different ones.

I'd missed finding lngMaxColumn in the subroutine I was having problems
with.

All works perfectly, now!!

Ian

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

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

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

Since I don't use Access, I'm glad your noticing my having used Set helped
you solve your problem as I wouldn't have what to tell you otherwise. For
your future consideration, the Set keyword is always used when assigning an
object reference to a variable or property.

Rick


"Ian" wrote in message
...
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