Offset

[Raman325]

Hi,

I am trying to use offset to traverse through a worksheet in which
there are five nonhidden columns and a bunch of hidden columns in
between. I have a range variable that stores the location of the first
column and first row (there are different titles and such at the top so
its not actually row 1). I thought that range = range.Offset(0,1) would
set the range to the location of the column to the immediate right of
the data, but for some reason it starts skipping columns. Why does it
do that? Thanks in advance.

 

[Tom Ogilvy]

It does refer to the column to the immediate right.

for example

cells(i,j).offset(0,1)

would refer to

cells(i,j+1)

Perhaps you can just use

cells(i,j+1)

 

[Guest]

Assuming your range variable is an actual range object such as
dim rng as range

then you can move the range object like this

set rng = rng.offset(0,1)

P.S. don't declare the range object as
dim Range as Range
Since range is a reserved word...

 

[Raman325]

Thanks for the responses. Yeah, I haven't been using range as the
variable name, I just used that as the example. That is exactly how I
had my macro set up, but it is still not going through the worksheet
correctly. I was playing around with the worksheet, moving left and
right with the arrow keys, and for some reason, its skipping over
columns. Why is it doing that? The right and left arrows arent working
as they should and I suspect that's what is causing the problem with
the offset.

 

[Tom Ogilvy]

No, right and left arrows do skip hidden columns, but this isn't the case
with offset.

? ActiveCell.Offset(0,1).EntireColumn.Hidden
True