When Bob and others say "Rarely if ever" should I use
select, then I am determined to eliminate it somehow.

I have tried EVERWAY I know in this situation and I just
can't get it.

Ok, the situation is this:

I'm in another worksheet, and I need to do this code:

Sub Upload_MJE_to_As400()
Range("Start").Select
Range(ActiveCell.End(xlDown),
ActiveCell.End(xlToRight)).Select

How do I get there and eliminate this select line in this
case when I'm somewhere else?

Bruce

Bruce,

Try something like

Dim Rng As Range
Set Rng = Range("Start")(1,1)
Range(Rng.End(xlDown),Rng.End(xlToRight)).Select


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



"Bruce Roberson" wrote in message
...
When Bob and others say "Rarely if ever" should I use
select, then I am determined to eliminate it somehow.

I have tried EVERWAY I know in this situation and I just
can't get it.

Ok, the situation is this:

I'm in another worksheet, and I need to do this code:

Sub Upload_MJE_to_As400()
Range("Start").Select
Range(ActiveCell.End(xlDown),
ActiveCell.End(xlToRight)).Select

How do I get there and eliminate this select line in this
case when I'm somewhere else?

Bruce

Hey Chip:

That worked, now help me understand the (1,1) part? Is
that the same as cells(1,1) except that you don't use
Cells since you're working with an object? Probably a dumb
question, but I'm still not comfortable with working with
objects and their syntax yet.

And I had misstated when I put
Range(ActiveCell.end(Xldown),
activecell.end(xltoright)).select

I changed that to ".copy" which is what I meant and that
worked fine that way.

So, selects are gone from that sub now. I only have 50,000
more to do away with that I have used while I've been
learning VBA.

Now, help me take this object to a different example, ok?

Sub CurrValues()
Sheets("MJE").Select
Range("Start").offset(2,.).Select
Selection.CurrentRegion.Clear

I tried the following replacement for the selects, but I
got a run time error 1004, application-defined or object
defined error.

The reason I keep selecting the sheet first and then the
range is because it doesn't always work unless I do it
that way. I think it has something to do with what sheet
is active at the time I run this sub. But it drives me
crazy when I see those error messages like that.

With Select:
Sub CurrValues()
Sheets("MJE").Select
Range("Start").Offset(2, 0).Select
Selection.CurrentRegion.Clear

Without Select:
Sub CurrValues()
Dim Rng as Range
Set Rng = Sheets("MJE").Range("MJE")(3,1)
Range(rng).Selection.CurrentRegion.Clear






-----Original Message-----
Bruce,

Try something like

Dim Rng As Range
Set Rng = Range("Start")(1,1)
Range(Rng.End(xlDown),Rng.End(xlToRight)).Selec t


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