VBA : エクセルの列を5列追加したいのですが・・・。

質問

VBAで、エクセルの列を5列追加したいのですが、うまくいく場合といかない場合があります。うまくいかない場合、エラーのダイアログボックスに

「オブジェクトシートからはみ出します。その操作ができません。」と・・・。

できる場合とできない場合の原因は特定できません。下のコードで列を追加していますが、やはりここでエラーが生じてしまいます。よろしければ助言を頂けたらと思います。

Range("A:E").Select
Selection.Insert

回答No.5

Wendy02 · ベストアンサー

e-l　様
返事が遅くなってすみません。

num = Application.CountA(Range("A:E").Offset(, 256 - 5))

＊

Application.CountA

は、WorksheetFunction.CountA (ワークシートの関数)のことですが、旧バージョンの書き方ですが、便利なことも多いので、Application.CountA を使っています。CountA の場合は、WorksheetFunction.CountA としても、変わりないはずです。

ただ、このApplication.[ワークシート関数] という使い方と、WorksheetFunction.[ワークシート関数]の使い方の相違点は、その戻り値のエラーの返し方です。一度、テストしてみると分かります。

関数にもよりますが、Application.[ワークシート関数]は、IsError(戻り値)で、検出できますが、WorksheetFunction.[ワークシート関数]は、実行時エラーになってしまいます。実行時エラーになった時は、On Error トラップでしか、解除できません。そのための補正コードを用意しなければなりません。

＊＊
Offset(, 256-5)
256-5 の意味はおわかりになっているでしょうけれども、
ヘルプをよくみていだくとわかりますが、

expression.Offset(RowOffset, ColumnOffset)

RowOffset 省略可能です。
ColumnOffset 省略可能です。

つまり、RowOffset側を省略しているのですね。
これは、私の持論なのですが、VBAなどのインタープリタ言語(スクリプト言語の部分もある)の場合は、なるべく「余計なことは書かない」というのが原則だと思います。そんなことをしても、千分の1秒にも満たないことですが、積もり積もれば、変わるかもしれない、ということです。

これは、単に、カッコウ付けだけではなく、こういう意図が含まれています。かく言う私は、以前、そういうコードをみて、カッコウ付けだけのようにみえて、そんなことをしなくても、分かりやすく書いて欲しいと思ってみてました。(^^;

回答No.4

Wendy02

#2 のWendy02 です。

＞プログラムの概要は、
＞5列追加して、そこに処理結果を表示します。そして、またプログラムを実行
＞させると、さらに5列追加されて、先ほどの処理結果は右にずれていきます。
＞新しい、処理結果は常に先頭に、古い処理結果は常に右にあるようにしていま
＞す。

最初から、それを書いてくれていたら、そのまま書けたのですが。もちろん、#2 を書くときに、今回の反応は多少の予想していたことですが。（^^;

Sub Sample2()
　Dim num As Long
　num = Application.CountA(Range("A:E").Offset(, 256 - 5))
　If num > 0 Then
　　If MsgBox("最終列5行を削除しますがよろしいですか?", _
　　　 vbQuestion + vbOKCancel) = vbOK Then
　　　Range("A:E").Offset(, 256 - 5).ClearContents
　　　Range("A:E").Insert
　　End If
　End If
End Sub

まあ、削除せずに、隣のシートに移したり、テキストに落としていく方法もありますね。

補足

お返事が遅くなりました。説明不足で大変申し訳御座いませんでした。的確にコードを作って頂いて感謝しております。

　宜しければ、コードが意味するところを教えて頂けると勉強になります。まず、

num = Application.CountA(Range("A:E").Offset(, 256 - 5))

は、何をしているのでしょうか？.CountAとOffset(, 256 - 5)の記述は初めて目にしました。offsetは分かるのですが（, 256-5)のカンマはどのような意味を持つのでしょうか。

小さいことで申し訳ありません。ヘルプを見たのですが、分かりませんでした。宜しくお願い致します。

回答No.3

bin-chan

> 何度かは、思い通りに動かすことができたのですが、今は動いてくれません。

Cｔｒｌ＋Ｅｎｄキーで、使用している(または使用した)範囲の最も右下のセルにジャンプできます。
値・式が無くても、過去の範囲を覚えているようです。

「今は動いてくれません」のシート上でこれを実行したとき、セルはどこが選択されますか？
思いのほか右側の列まで「使用した」ことになっていませんか？

もしもそうなら、「ご自身が使用した最も右端の列」＋１列～「ＥＸＣＥＬが覚えている使用したことになっている列」までを選択し、「削除－列全体」削除を行い、さらにその直後に「上書き保存」してください。
※列削除の直後に「上書き保存」することがポイントです。
その後、Cｔｒｌ＋Ｅｎｄキーで、使用した範囲の最も右下のセルを確認してみてください。

お礼

有難う御座います。この方法で一応できました。しかし、私のコードを用いて他人も使うので予めコードで、このようなことが起こらないようにしたいと考えています。

回答No.2

Wendy02

右側の該当セルに何か入っていたら、Insert を取りやめにするか、その範囲をあらかじめ、Clearでもするか、どちらかになるのではないでしょうか？

例:

Sub test()
Dim num As Long
num = Application.CountA(Range("A:E").Offset(, 256 - 5))
If num = 0 Then
　　Range("A:E").Insert
End If
End Sub

補足

ご返答有難うございます。右側の該当セルに何か入っていたことが問題なのでしょうか？

プログラムの概要は、
5列追加して、そこに処理結果を表示します。そして、またプログラムを実行させると、さらに5列追加されて、先ほどの処理結果は右にずれていきます。新しい、処理結果は常に先頭に、古い処理結果は常に右にあるようにしています。

何度かは、思い通りに動かすことができたのですが、今は動いてくれません。

回答No.1

bin-chan

シートの横（列）は～IVまでです。

ＩＲ列～ＩＶ列までに何がしかのデータがあると５列追加で制限を越えるために発生していると思います。

MsgBox Worksheets("Sheet1").UsedRange.Address

で、使用している範囲がわかるので事前にチェックかな？

補足

有難うございます。しかし、このコードを実行する前に、シートを一度全削除して（一新して）やってみましたがそれでも駄目でした。