Run Time Error 9 Compile Error

[Guest]

I posted this by accident in the wrong newsgroup. Here is the repost.

I just turned an xls file into an xla file and am having some issues with my
code. The code snippet below is failing on: Windows(varfile1).Activate.
varfile1 is the name of the xla file. When it was an xls file I selected the
workbook, then selected a sheet in it, then selected a start cell and then
figured out what the used range was (I know I can also employ .UsedRange but
will ignore for now). However, now that its an xla file I cannot seem to a
get to my start cell (within a sheet in the xla file) by selecting the xla
file and its sheet. The goal is to conitnue to correctly pass the right
values to the two variables LRow1 and LCol1

As I cannot select hidden objects, I need a way to do this. Or am I forced
to change this all around and used the .UsedRange property?

Windows(varfile1).Activate
Sheets(varsheet1).Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
With Selection
.MergeCells = False
End With
Range("A1").Select
LRow1 = ActiveCell.SpecialCells(xlLastCell).Row
LCol1 = ActiveCell.SpecialCells(xlLastCell).Column

 

[Tom Ogilvy]

Dim rng as Range, rng1 as Range
Dim sh as Worksheet
set rng = Workbooks(varfile1).Sheets(varsheet1).Range("A1")
set sh = rng.parent
set rng1 = sh.Range(rng, rng.SpecialCells(xlLastCell))
rng1.MergeCells = False
LRow1 = rng.SpecialCells(xlLastCell).Row
LCol1 = rng.SpecialCells(xlLastCell).Column

Not sure what you are trying to avoid with UsedRange. xlLastCell is no more
accurate.

 

[Chip Pearson]

XLA files are hidden, so you can't use Activate or Select on a
cell or sheet in the XLA.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


message
news:[email protected]...

 

[Guest]

I am actually tyring to avoid completely rewriting this code for now. I have
had this code given to me and I have to integrate it with some of my own
code. I am strapped for time and want the authour to come back later and
spruce it up. In the mean time, I need to band-aid it so the merged XLA can
be released amongst a small team. Not ideal but then again, not my idea!!!!
Thanks for tip. Will look at it.

EM