[RESOLVED] Excel Range of Cells selection doesn't work

[MartinLiss]

This works
ActiveSheet.Range("H4:H10").Select

This gives application-defined or object-defined error. Why????
ActiveSheet.Range(Cells(4, 8), Cells(10, 8)).Select

[Zvoni]

Cells of what?

[westconn1]

ActiveSheet.Range(Cells(4, 8), Cells(10, 8)).Select

i would assume if you fully qualify the cells it should work
the above code leaves a possibility that the cells could be on a different sheet to the range object, even though the default for unqualified cells would be the default (active) worksheet

[MartinLiss]

Cells of what?

The cells of the active sheet of an Excel workbook.

[MartinLiss]

i would assume if you fully qualify the cells it should work
the above code leaves a possibility that the cells could be on a different sheet to the range object, even though the default for unqualified cells would be the default (active) worksheet

What do you mean when you say "fully qualify"? And why is the Range Cells index method different from the Range A1 method?

[si_the_geek]

For Range you specified the parent object ( ActiveSheet.Range ), but for Cells you didn't.

If you don't specify a parent, Excel will make a guess... and there are some oddities to that which can cause errors or bugs.


Try this:

 ActiveSheet.Range(ActiveSheet.Cells(4, 8), ActiveSheet.Cells(10, 8)).Select

By the way, it is best to also avoid selecting things if you can, as it is slow, and the current selection can change at any moment outside of your control (particularly if the user or other code is working with Excel).

Generally you can easily avoid it by merging two lines of code (removing .Select and Selection), so if your code is like this:

ActiveSheet.Range("H4:H10").Select
Selection.Paste

...the quicker/safer equivalent is this:

ActiveSheet.Range("H4:H10").Paste

[MartinLiss]

ActiveSheet.Range(ActiveSheet.Cells(4, 8), ActiveSheet.Cells(10, 8)).Select

Thanks Si, I'm sure that's it. In my code I bounce around between sheets and I ran into other places where after selecting a given sheet I had to use ActiveSheet even though I had explicitly selected the sheet. I just never thought of using ActiveSheet within the range. I'll keep the other information in mind too.

[westconn1]

ActiveSheet even though I had explicitly selected the sheet.

you should avoid using activesheet, better to use fully qualified ranges book-sheet-range, or better, set worksheet objects and work with those through the code

What do you mean when you say "fully qualify"?

sorry i did not explain more fully

the default sheet is normally the active sheet, but in cases it has just been selected or activated, it may not be until the code finishes or a doevents is processed

I just never thought of using ActiveSheet within the range.

easy in a with block

with somesheet
   .range(.cells(2, 1), .cells(4,2)).select
end with

but best to avoid selecting

[MartinLiss]

Si (or westconn1), I'm getting back to this and I find that I'm still confused. Can you show me a complete set of code that specifies both the source and destination sheets?

[westconn1]

the best way is to totally avoid selecting or activating sheets or ranges, just use full qualified ranges

fully qualified should be from workbook level down like
workbooks("bookx.xls").sheets("mysheet").range("a3").copy

when working with multiple sheets, either within the same book or not, best to assign a worksheet objects

set somesht = workbooks("bookx.xls").sheets("mysheet")
with somesht
   .range(.cells(2, 2)), .cells(2, 4)).copy
   ' you can see that outside a with block, you should have to specify the book /sheet (or sheet object) for each range of cells, to be on the same sheet as the parent range
end with
secondsht.cells(4, 6).pastespecial
' where secondsht was previously assigned and target range is a single cell (for paste)