Selecting a Range of cells in VBA

S

spacecityguy

Hello,

I keep having problem with the range() method in VBA. While it seems t
be a straightforward method, I cannot apply it with consistent success
Even worse, I trigger the command the same way, but sometimes it work
fine while other times I kept getting the "select method of range clas
failed". For example, I created a button that had only these tw
lines:

sheet9.activate
sheet9.range("a10:e15").select

These two lines worked fine, but I don't understand why I had t
activate the sheet since I already specify the sheet to look for in m
range() method.

Even worse, when I tried to use the cells() method to specify th
range:

sheet9.activate
sheet9.range(cells(10,1),cells(15,5)).select

the abovementioned error occur. I have no idea why the failure or ho
to fix the problem. I just don't understand. Every single book I hav
talks about the range method as though it's the simplest method ther
is (that's what I thought too).

I would truly appreciate if anybody can explain this to me
 
V

Vasant Nanavati

You can't select a range on an inactive sheet.

I could not replicate your second problem.
 
D

Dave Peterson

I'm guessing that this code is behind a worksheet (not sheet9, too).

sheet9.activate
sheet9.range(cells(10,1),cells(15,5)).select

Unqualified ranges belong to the worksheet that holds the code.

so if this code were behind Sheet1, you're really typing:

sheet9.range(sheet1.cells(10,1),sheet1.cells(15,5)).select

you could qualify it longhand:
sheet9.range(sheet9.cells(10,1),sheet9.cells(15,5)).select

but maybe easier reading/typing:

with sheet9
.range(.cells(10,1),.cells(15,5)).select
end with

That said, there's not a lot that you have to select to work on.

You could just work on things directly:

with sheet9
.range(.cells(10,1),.cells(15,5)).clearcontents
end with
and drop the .selects and .activates.
 
Top