[[20210405165551]]　『列の挿入をするとエラーになる』（ぴーちゃん）　ページの最後に飛ぶ

『列の挿入をするとエラーになる』（ぴーちゃん）

お世話になっております。
シートにデータを転記したのち、そのシートを加工するために
列を挿入しています。

1行のみ挿入する場合
Columns("S:S").Insert Shift:=xlToRight
複数行挿入する場合
Columns("AD:CA").Insert Shift:=xlToRight

上記マクロで列を挿入しているのですが、
実行時エラー　１００４
空でないセルをワークシートの外に押し出してしまうため、新しいセルを挿入できません。
空のように見えるセルであっても、空白の値、書式、または数式が含まれている場合があります。
挿入するための領域を確保できるように十分な行または列を削除してから、もう一度やり直してください。

というエラーメッセージが出てしまいます。

新しいシートで試したりしたのですが、このエラーがでてしまいます。
ご教示いただきたく、宜しくお願いいたします。

＜使用 Excel：unknown、使用 OS：unknown ＞

 いらんのが付いてます。
 ↓だけでいけるはずです。

Columns("AD:CA").Insert
（通りすがり） 2021/04/05(月) 17:20

 とりあえず、イミディエイトウィンドウに
 これを入れると、使用範囲が出ますので、どうなっているのかチェックしてみたらどうですか？
 　↓
 ? ActiveSheet.UsedRange.Address

 右端いっぱいになっていますかね？

（半平太） 2021/04/05(月) 17:23

 衝突しましたがそのままアップします。

 ↑は間違いでした。

 メッセージのとおり、列挿入することによって、列限界より外に
 押し出されるセルが存在するので警告を発してます。
（通りすがり） 2021/04/05(月) 17:26

新しいシートでも出るというのが不思議ですね
コピーしたセルではないんですよね？
（砂糖） 2021/04/05(月) 17:30

↑コピーしたセルってなんだよ・・・
コピーしたシートでした失礼しました
（砂糖） 2021/04/05(月) 17:31

 新規シートでも出ますか。見落としてました。
 新規ブックでも同じですか？
（通りすがり） 2021/04/05(月) 17:34

ありがとうございます。

半平太様
ご回答いただきありがとうございます。
イミディエイトウィンドウは、ほぼ使ったことないのですが
下記入力し、エンターキーを押しました。
? ActiveSheet.UsedRange.Address

$A$1:$IT$44 → 当初はこのように表示されましたが、
$1:$44 → 今はこの表示しか出なくなってしまいました。

すみません。状況お分かりになりますでしょうか？
（ぴーちゃん） 2021/04/05(月) 17:57

通りすがり様
新規シートではエラーになりました。
新規ブックでは、試していないです。
（ぴーちゃん） 2021/04/05(月) 18:00

 1行目～44行目全体に書式が設定された、ということも
 考えられます。
 新規シートで試した、ということですが、新規シートの挿入
 は手作業ですか？マクロですか？
（通りすがり） 2021/04/05(月) 18:25

 まさか、とは思いますが、CTRLキーを押しながらシートタブ
 ドラッグでシートをコピーしたものを「新規シート」と呼んでる、
 ということはありませんか？
（通りすがり） 2021/04/05(月) 18:27

 エラーの出る行だけでなく、マクロ全体を書き込んだ方がいいです
 エラーの原因がエラー発生行以外にあることは良くあります。

 あるあるとしては、
 Range("ほげ").End(xlToRight) のセルを操作したりなんかしてたり、

 姑息的手段としては、
  Columns(Columns.Count - 50).Resize(, 50).Delete
  Columns("AD:CA").Insert Shift:=xlToRight
 でエラー回避できますが、
 抜本的に見直さないと副作用があるかもしれません。
（´・ω・`） 2021/04/05(月) 18:42

乱暴なうえに根本的な解決じゃないけど、あらかじめ挿入してあふれる分をクリアしておくのはだめですか？

    Sub test()
        With ActiveSheet
            .Columns(.Columns.Count).Offset(, -49).Resize(, 50).Clear
            .Columns("AD:CA").Insert Shift:=xlToRight
        End With
    End Sub

（もこな２） 2021/04/05(月) 21:52

ご回答ありがとうございます。
シートにデータを転記した後、というのは下記マクロで作成したものを指しています。
リスト１から、リスト２で指定したものを抜き出しています。
抜き出し先はリスト３です。
リスト３は、列A~BCまであり、行は抜き出すものにより異なっています。

Sub test()

    Sheets("list3").Select

    Dim Wsh1 As Worksheet
    Dim Wsh2 As Worksheet
    Dim TgtRng As Range
    Dim CriRng As Range

    Set Wsh1 = Sheets("list1")
    Set Wsh2 = Sheets("list2")

    Set TgtRng = Wsh1.Range("A1:BF" & Wsh1.Cells(Wsh1.Rows.Count, "G").End(xlUp).Row)
    Set CriRng = Wsh2.Range("B2", Wsh2.Range("B2").End(xlDown))

    With Sheets("list3")
        .UsedRange.ClearContents
        TgtRng.AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=CriRng, CopyToRange:=.Range("A1"), Unique:=False
    End With

End Sub

その後、まず、左端に２行挿入したいのですが、
この時点でも、エラーがでるようになりました。（初めはここではエラーは出なかったのですが、今はでます。）

    '2行挿入
    Columns("A:A").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

その後、不要な行を削除したのち、
こちらで質問させていただいた行の挿入をすると、エラーがでます。
（いつも同じ箇所でエラーが出るのではなく、マクロを起動すると、どこかの行挿入でエラーが出ている状態です）

1行のみ挿入する場合
Columns("S:S").Insert Shift:=xlToRight
複数行挿入する場合
Columns("AD:CA").Insert Shift:=xlToRight
実際は、上記よりもっと挿入する行は多く、最終的にリスト３は、A~CH列まであります。（もう数行増える予定です）

新規シートは、手作業で、バツボタンを押して新規シートを作成しました。
回答いただいたものすべてを試せていなくすみません。
取り急ぎ、ご連絡させていただきます。
ご教示いただきたく宜しくお願いいたします。

（ぴーちゃん） 2021/04/06(火) 23:52

2021/04/05(月) 21:52の投稿について、日中に書き溜めたものを投稿したら（´・ω・`）さんとほぼ丸被りでした。すみません。

エラーの原因は皆さんがおっしゃる通りだとおもいますが、↓を実行するとどうなりますか？

    Sub さんぷる()        
        Dim データ範囲 As Range
        Dim 抽出条件 As Range

        With Sheets("list1")
            Set データ範囲 = .Range("A1:BF" & .Cells(.Rows.Count, "G").End(xlUp).Row)
        End With

        With Sheets("list2")
            Set 抽出条件 = .Range("B2", .Range("B2").End(xlDown))
        End With

        With Sheets("list3")
            .UsedRange.Clear '←ClearContentsから変更

             データ範囲.AdvancedFilter _
                Action:=xlFilterCopy, _
                CriteriaRange:=抽出条件, _
                CopyToRange:=.Range("C1") '←直後に２【列】挿入するなら初めからC列に出力すべき

            '▼50【列】挿入
            .Range("AD").EntireColumn.Resize(, 50).Insert
        End With
    End Sub

（もこな２） 2021/04/07(水) 05:56

失礼。↑について一部に誤りがありました。

 誤 .Range("AD").EntireColumn.Resize(, 50).Insert
 正 .Range("AD1").EntireColumn.Resize(, 50).Insert

（もこな２） 2021/04/07(水) 19:33

ありがとうございます。無事起動できました。
よくわかっていなくて申し訳ないのですが、もこな２さん書いていただいたマクロのどこが
良くて列が挿入できるのでしょうか？
（自分の当初書いたマクロのどこが悪くて列が挿入できなかったのでしょうか？）
お手数おかけしますが、ご教示いただけますと助かります。

（ぴーちゃん） 2021/04/08(木) 17:56

既に答えがあったように、なんらかの原因で行全体に書式設定されてしまったんでしょう。
そのために、エクセル君が最終列まで使用していると認識してしまい、列挿入するとデータを押し出すことになるので処理できないというエラーを提示しているのですから、

 .UsedRange.Clear '←ClearContentsから変更

↑のように、Clearメソッドに変更し、書式も含めてクリアしたわけです。

（もこな２） 2021/04/08(木) 19:41

ご丁寧にありがとうございます。
大変助かりました。どうもありがとうございました。
（ぴーちゃん） 2021/04/08(木) 20:47

コメント返信：

[ 一覧(最新更新順) ]

[[20210405165551]] 『列の挿入をするとエラーになる』（ぴーちゃん） ページの最後に飛ぶ

[[20210405165551]]　『列の挿入をするとエラーになる』（ぴーちゃん）　ページの最後に飛ぶ