named range/offset

[greg]

Hello,
I was playing around with the offset. Wanted to get users opinions of
offset.

If I have a named range. and I want to get the row above, below, etc...
Do people like using offset?

It seems like I can do,

Range("myRange").Offset(-1)
and it will give me the range = same column and 1 row above.

is this bad to do?
thanks

 

[RyanH]

In my opinion there is no problem at all with using Offset. You do need to
make a correct to your code below.

Dim myRange as Range

Set myRange = Range("A1:B2") 'or some other range

Range("myRange").Offset(-1)

Hope this helped!

 

[StumpedAgain]

Hey Greg,

Personally, I love offset. It is especially useful (for what I've done) in
loops with copying/pasting so I'll have:

curselection.Copy Range("A7").Offset(i +1, 0)

where i is the loop number. Some people may not like it, but I have found
it very useful.

Just my two cents...

 

[JLGWhiz]

When I was a beginner, any code that worked was good code, as far as I was
concerned. Today, any code that works is good code, as far as I am
concerned. There is always somebody who can make it look better, run faster
or some other frill. Unless you are writing code commercially, including the
company you work for, frills are just that. When you learn more, get more
confidence in your abilities and have a data base of references for code
structure, storage space conservation, execution speeds and so on, you might
want to be more concerned about applying those things. In the mean time,
when it works, rejoice.

 

[Bob Phillips]

What other choices do you have?

maybe

Range("myRange").Cells(0,1)

but that is horrible.

 

[Jim Thomlinson]

Have you tried that code??? It does not look like it will work to me...

Dim myRange as Range 'declare a range object

Set myRange = Range("A1:B2") 'define the range object
Range("myRange").Offset(-1) 'myRange is now text

When you put myRange in quotes it is now looking for a Named Range (which is
what Greg originally had which is just fine). But that has nothing to do with
your range object. Assuming you remove the quotes then the range object is
used but the default property of a range object is it's value and not it's
address so you still get the wrong result. Finally Assuming that you had

myRange.offset(-1)

which would correctly use your range object you would get a subscrpit out of
range error as you are trying to offset right off of the sheet.


All of this ignores that
Range("myRange").Offset(-1)
results in specifying a range which is not used...

 

[Rumplestiltskin]

I use the offset funtion frequently in combination with CurrentCell

Set CurrentCell = Worksheets("Whatever").Range("$B$4") 'or named range
Do Until IsEmpty(CurrentCell)
Set CurrentCell = CurrentCell.Offset(1, 0)
Loop

I like it because it doesn't really select the cell. This probably
isn't a good example but it does what I need it to do.

 

[greg]

thanks all for the help