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

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" wrote in
message ...

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

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

Another option is to just use the entirerow:

Range("B26:G26").Resize(Range("b25").Value).Insert
becomes
Range("B26:G26").Resize(Range("b25").Value).entire row.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

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" wrote in message
...
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

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


Thank You,
Chris :

--
zero63
-----------------------------------------------------------------------
zero635's Profile: http://www.excelforum.com/member.php...fo&userid=2480
View this thread: http://www.excelforum.com/showthread.php?threadid=38804