Insert Function Problem

[zero635]

Ok, I am using the following code to insert rows below a header.

Sub InsertRange()

Range("B26:G26").Resize(Range("b25").Value).Insert

End Sub

Well, with a value of 8 or greater in cell "B25", my data below it shifts to the right.

With a value of 7 or less it shifts the data below down and gives me the empty spaces I need.

Can anyone tell me why a value of 8 or greater would cause this formula to shift my data to the right, instead of down like a normal insertion?

Thank You,
Chris

[Norman Jones]

Hi Chris,

In the absence of any stipulation by you, Excel makes a guess as to which
way to shift your data. How Excel makes that guess depends on the
configuration of your data. As with most Excel guesses, they are sometimes
right and frequently wrong. The answer, in this case, is to be explicit.

You can provide this information by using the optional shift argument to the
insert method, e.g.:

Range("B26:G26").Resize(Range("b25").Value).Insert shift:=xlDown


---
Regards,
Norman



"zero635" <[email protected]> wrote in
message news:[email protected]...
>
> Ok, I am using the following code to insert rows below a header.
>
> Sub InsertRange()
>
> Range("B26:G26").Resize(Range("b25").Value).Insert
>
> End Sub
>
> Well, with a value of 8 or greater in cell "B25", my data below it
> shifts to the right.
>
> With a value of 7 or less it shifts the data below down and gives me
> the empty spaces I need.
>
> Can anyone tell me why a value of 8 or greater would cause this formula
> to shift my data to the right, instead of down like a normal insertion?
>
> Thank You,
> Chris
>
>
> --
> zero635
> ------------------------------------------------------------------------
> zero635's Profile:
> http://www.excelforum.com/member.php...o&userid=24802
> View this thread: http://www.excelforum.com/showthread...hreadid=388048
>

[Jim Thomlinson]

Unless you specifically tell Excel what to do then it makes best guesses for
you. Depending on what your data looks like it will decide whether to shift
down of to the right. Change your code to be ...

Range("B26:G26").Resize(Range("b25").Value).Insert xlDown
or
Range("B26:G26").Resize(Range("b25").Value).Insert xlToRight

Depending on what you want to do...
--
HTH...

Jim Thomlinson


"zero635" wrote:

>
> Ok, I am using the following code to insert rows below a header.
>
> Sub InsertRange()
>
> Range("B26:G26").Resize(Range("b25").Value).Insert
>
> End Sub
>
> Well, with a value of 8 or greater in cell "B25", my data below it
> shifts to the right.
>
> With a value of 7 or less it shifts the data below down and gives me
> the empty spaces I need.
>
> Can anyone tell me why a value of 8 or greater would cause this formula
> to shift my data to the right, instead of down like a normal insertion?
>
> Thank You,
> Chris
>
>
> --
> zero635
> ------------------------------------------------------------------------
> zero635's Profile: http://www.excelforum.com/member.php...o&userid=24802
> View this thread: http://www.excelforum.com/showthread...hreadid=388048
>
>

[zero635]

Thank you guys for the response. I am fairly new to this programming stuff. I am learning though. I have come a long way, but apparently not far enough. I appreciate the time you have taken to give me a hand.


Thank You,
Chris

[alf bryn]

Experimenting a bit it seems to me that Excel shifts the data to the right
if the number in B25 > than the number of columns in the range (i.e B to G =
6) .

For values of B25=6 or less data gets shifted down.

As Jim and Norman said. Excel moves in mysterious ways it's wonders to
preform.


"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Unless you specifically tell Excel what to do then it makes best guesses
> for
> you. Depending on what your data looks like it will decide whether to
> shift
> down of to the right. Change your code to be ...
>
> Range("B26:G26").Resize(Range("b25").Value).Insert xlDown
> or
> Range("B26:G26").Resize(Range("b25").Value).Insert xlToRight
>
> Depending on what you want to do...
> --
> HTH...
>
> Jim Thomlinson
>
>
> "zero635" wrote:
>
>>
>> Ok, I am using the following code to insert rows below a header.
>>
>> Sub InsertRange()
>>
>> Range("B26:G26").Resize(Range("b25").Value).Insert
>>
>> End Sub
>>
>> Well, with a value of 8 or greater in cell "B25", my data below it
>> shifts to the right.
>>
>> With a value of 7 or less it shifts the data below down and gives me
>> the empty spaces I need.
>>
>> Can anyone tell me why a value of 8 or greater would cause this formula
>> to shift my data to the right, instead of down like a normal insertion?
>>
>> Thank You,
>> Chris
>>
>>
>> --
>> zero635
>> ------------------------------------------------------------------------
>> zero635's Profile:
>> http://www.excelforum.com/member.php...o&userid=24802
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=388048
>>
>>

[Dave Peterson]

Another option is to just use the entirerow:

Range("B26:G26").Resize(Range("b25").Value).Insert
becomes
Range("B26:G26").Resize(Range("b25").Value).entirerow.Insert
Or
Range("B26").Resize(Range("b25").Value).entirerow.Insert
(since B26:G26 is just one row)

Since you're using the entirerow, there's only one way you can shift it--down.

zero635 wrote:
>
> Ok, I am using the following code to insert rows below a header.
>
> Sub InsertRange()
>
> Range("B26:G26").Resize(Range("b25").Value).Insert
>
> End Sub
>
> Well, with a value of 8 or greater in cell "B25", my data below it
> shifts to the right.
>
> With a value of 7 or less it shifts the data below down and gives me
> the empty spaces I need.
>
> Can anyone tell me why a value of 8 or greater would cause this formula
> to shift my data to the right, instead of down like a normal insertion?
>
> Thank You,
> Chris
>
> --
> zero635
> ------------------------------------------------------------------------
> zero635's Profile: http://www.excelforum.com/member.php...o&userid=24802
> View this thread: http://www.excelforum.com/showthread...hreadid=388048

--

Dave Peterson