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

 

[Guest]

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

 

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

 

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

 

[zero635]

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 :