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)

--
Regards,
Tom Ogilvy


"Raman325" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> Raman325
> ------------------------------------------------------------------------
> Raman325's Profile:
http://www.excelforum.com/member.php...o&userid=24748
> View this thread: http://www.excelforum.com/showthread...hreadid=396202
>

[Jim Thomlinson]

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...
--
HTH...

Jim Thomlinson


"Raman325" wrote:

>
> 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.
>
>
> --
> Raman325
> ------------------------------------------------------------------------
> Raman325's Profile: http://www.excelforum.com/member.php...o&userid=24748
> View this thread: http://www.excelforum.com/showthread...hreadid=396202
>
>

[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


--
Regards,
Tom Ogilvy

"Raman325" <[email protected]> wrote in
message news:[email protected]...
>
> 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.
>
>
> --
> Raman325
> ------------------------------------------------------------------------
> Raman325's Profile:
http://www.excelforum.com/member.php...o&userid=24748
> View this thread: http://www.excelforum.com/showthread...hreadid=396202
>