Trouble Selecting a Range in VBA
 

I have a routine in VBA (run from MS Access) that generates s spreadsheet and
then applies an Excel subtotal function. Once the sub totals are created I
collapse to the 2nd level. This is where I run into problems. What I want to
do is select the visible cells between a1 and my last cell so that I can
apply some formatting. I know how to get to the last cell and how to get to
the visible cells but when I try to select my range I get an error "object
does not support this method or property".

stRange = "A1:" & activecell.address

Any help you give would be much appreciated.


--
Michael Joyce
Project Management IS Analyst
Aflac - Project Management Office

Trouble Selecting a Range in VBA
 

You will probably need to qualify activecell with the application.object.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MJatAflac" wrote in message
...
I have a routine in VBA (run from MS Access) that generates s spreadsheet
and
then applies an Excel subtotal function. Once the sub totals are created I
collapse to the 2nd level. This is where I run into problems. What I want
to
do is select the visible cells between a1 and my last cell so that I can
apply some formatting. I know how to get to the last cell and how to get
to
the visible cells but when I try to select my range I get an error "object
does not support this method or property".

stRange = "A1:" & activecell.address

Any help you give would be much appreciated.


--
Michael Joyce
Project Management IS Analyst
Aflac - Project Management Office

Trouble Selecting a Range in VBA
 

That did it!

Thanks a million!
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


"Bob Phillips" wrote:

You will probably need to qualify activecell with the application.object.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"MJatAflac" wrote in message
...
I have a routine in VBA (run from MS Access) that generates s spreadsheet
and
then applies an Excel subtotal function. Once the sub totals are created I
collapse to the 2nd level. This is where I run into problems. What I want
to
do is select the visible cells between a1 and my last cell so that I can
apply some formatting. I know how to get to the last cell and how to get
to
the visible cells but when I try to select my range I get an error "object
does not support this method or property".

stRange = "A1:" & activecell.address

Any help you give would be much appreciated.


--
Michael Joyce
Project Management IS Analyst
Aflac - Project Management Office

Trouble Selecting a Range in VBA
 

I am not sure how you have declared your StRange Variable. The
following example does work though...

Dim stRange As String

stRange = "A1:" & ActiveCell.Address
Debug.Print stRange
Range(stRange).Select

I assumed you were using a string and not a Range Object. Hope that
helps

Trouble Selecting a Range in VBA
 

You're right, I was using a string.
I just had to qualify it more.

dim stRange as string
stRange = "a1:" & xlApp.ActiveCell.Address
--
Michal Joyce
Project Management IS Analyst
Aflac - Project Management Office


"Mark" wrote:


I am not sure how you have declared your StRange Variable. The
following example does work though...

Dim stRange As String

stRange = "A1:" & ActiveCell.Address
Debug.Print stRange
Range(stRange).Select

I assumed you were using a string and not a Range Object. Hope that
helps

Trouble Selecting a Range in VBA
 

I'm not sure what code you used to (unsuccessfully) try to select your range,
but when I used your statement:

stRange = "A1:" & activecell.address

and then:

range(stRange).Select

the range was selected just fine.

"MJatAflac" wrote:

I have a routine in VBA (run from MS Access) that generates s spreadsheet and
then applies an Excel subtotal function. Once the sub totals are created I
collapse to the 2nd level. This is where I run into problems. What I want to
do is select the visible cells between a1 and my last cell so that I can
apply some formatting. I know how to get to the last cell and how to get to
the visible cells but when I try to select my range I get an error "object
does not support this method or property".

stRange = "A1:" & activecell.address

Any help you give would be much appreciated.


--
Michael Joyce
Project Management IS Analyst
Aflac - Project Management Office