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

Question

VBAで、エクセルの列を5列追加したいのですが、うまくいく場合といかない場合があります。うまくいかない場合、エラーのダイアログボックスに

「オブジェクトシートからはみ出します。その操作ができません。」と・・・。

できる場合とできない場合の原因は特定できません。下のコードで列を追加していますが、やはりここでエラーが生じてしまいます。よろしければ助言を頂けたらと思います。

    Range("A:E").Select
    Selection.Insert

Wendy02 · Accepted Answer

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秒にも満たないことですが、積もり積もれば、変わるかもしれない、ということです。

これは、単に、カッコウ付けだけではなく、こういう意図が含まれています。かく言う私は、以前、そういうコードをみて、カッコウ付けだけのようにみえて、そんなことをしなくても、分かりやすく書いて欲しいと思ってみてました。(^^;

Wendy02 · Answer

#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

まあ、削除せずに、隣のシートに移したり、テキストに落としていく方法もありますね。

bin-chan · Answer

> 何度かは、思い通りに動かすことができたのですが、今は動いてくれません。

Cｔｒｌ＋Ｅｎｄキーで、使用している(または使用した)範囲の最も右下のセルにジャンプできます。
値・式が無くても、過去の範囲を覚えているようです。

「今は動いてくれません」のシート上でこれを実行したとき、セルはどこが選択されますか？
思いのほか右側の列まで「使用した」ことになっていませんか？

もしもそうなら、「ご自身が使用した最も右端の列」＋１列～「ＥＸＣＥＬが覚えている使用したことになっている列」までを選択し、「削除－列全体」削除を行い、さらにその直後に「上書き保存」してください。
※列削除の直後に「上書き保存」することがポイントです。
その後、Cｔｒｌ＋Ｅｎｄキーで、使用した範囲の最も右下のセルを確認してみてください。

Wendy02 · Answer

右側の該当セルに何か入っていたら、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

bin-chan · Answer

シートの横（列）は～IVまでです。

ＩＲ列～ＩＶ列までに何がしかのデータがあると５列追加で制限を越えるために発生していると思います。

MsgBox Worksheets("Sheet1").UsedRange.Address

で、使用している範囲がわかるので事前にチェックかな？

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

e-l 様

#2 のWendy02 です。

この回答への補足

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

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

この回答への補足

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

この回答への補足

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

e-l　様