Range as cell numbers

[Ian]

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

 

[Jim Thomlinson]

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

 

[perjessen69]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

With objExcel

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

I get "Sub or Function not defined" as Access doesn't understand "Cells"

Ian

 

[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

 

[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

 

[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\)]

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]

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.

 

[Rick Rothstein \(MVP - VB\)]

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]

Thanks, Rick.

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