(Excel VBA)RangeプロパティでCellsやRows、Columnsを組み合わせて使うと「実行時エラー 1004」が出る時の対処法

スポンサードリンク

仕事でよく使うVBAなんですが、どうしてもRangeプロパティを使わないといけない時に変数を使おうとするとRangeプロパティにセル番地を指定するCellsプロパティや
行の指定をするRowsプロパティ、列を指定するColumnsプロパティを組み合わせて使うことがあります。

マクロを実行したら「実行時エラー 1004」が出て困ることがよくあるんです。

これを見てくれている方も同じ悩みだと思います。

今回、RangeプロパティでCellsやRows、Columnsを組み合わせて使うと「実行時エラー 1004」が出る時の対処法を覚書で残しておきます。

スポンサードリンク

RangeプロパティでCellsやRows、Columnsを組み合わせて使うと「実行時エラー 1004」が出る

このようなデータが入っているシートに対してVBAで範囲指定して削除するとします。

下記のように、例えばSheet1のある範囲のデータの値を削除するVBAを標準モジュール記述したものを【Sheet2】をアクティブにして実行します。

Public Sub test()

    ThisWorkbook.Worksheets("Sheet1").Range("A1:C10").ClearContents 'A1～C10の範囲
    ThisWorkbook.Worksheets("Sheet1").Range("D:F").ClearContents 'D～F列すべて
    ThisWorkbook.Worksheets("Sheet1").Range("11:15").ClearContents '11行目から15行目

End Sub

こんな感じに指定範囲が削除されます。

このVBAを変数を使うためにCellsやRows、Columnsを組み合わせてみます。

変数を使って同じ結果になるように標準モジュールに記述したものを【Sheet2】をアクティブにして実行します。

Public Sub test2()
    Dim i As Long
    Dim j As Long
    Dim k As Long

    i = 10
    j = 6
    k = 15

    ThisWorkbook.Worksheets("Sheet1").Range(Cells(1, 1), Cells(i, 3)).ClearContents 'A1～C10の範囲:Cells使用
    ThisWorkbook.Worksheets("Sheet1").Range(Columns(4), Columns(j)).ClearContents 'D～F列すべて
    ThisWorkbook.Worksheets("Sheet1").Range(Rows(11), Rows(k)).ClearContents '11行目から15行目
End Sub

すると結果は「実行時エラー 1004」が出てしまいます。

Rangeプロパティ内をCellやColumns、Rowsで変更して修正すると起きてしまう現象です。

僕もよくやってしまいます。

「実行時エラー 1004」が出て、いつも気付くのに遅くなってしまいます。

では、この「実行時エラー 1004」の解決法です。

「実行時エラー 1004」の解決法

先ほどの「実行時エラー 1004」を解決するために、下記のように修正します。

Public Sub test3()
    Dim i As Long
    Dim j As Long
    Dim k As Long

    i = 10
    j = 6
    k = 15

    ThisWorkbook.Worksheets("Sheet1").Range(ThisWorkbook.Worksheets("Sheet1").Cells(1, 1), ThisWorkbook.Worksheets("Sheet1").Cells(i, 3)).ClearContents 'A1～C10の範囲:Cells使用
    ThisWorkbook.Worksheets("Sheet1").Range(ThisWorkbook.Worksheets("Sheet1").Columns(4), ThisWorkbook.Worksheets("Sheet1").Columns(j)).ClearContents 'D～F列すべて
    ThisWorkbook.Worksheets("Sheet1").Range(ThisWorkbook.Worksheets("Sheet1").Rows(11), ThisWorkbook.Worksheets("Sheet1").Rows(k)).ClearContents '11行目から15行目
End Sub

何を修正したかというと、Rangeプロパティ内のCellやColumns、Rowsにどのシートなのか場所を教えてあげないといけないので、【ThisWorkbook.Worksheets(“Sheet1”).】をRangeプロパティ内のCellやColumns、Rowsに指定しただけです。

どのシートなのか場所を教えてあげないと、Rangeプロパティはシート場所を指定しているのに、Rangeプロパティ内のCellやColumns、Rowsは場所がないのでアクティブシートのセルを選択することになってしまいます。

つまり、『Sheet2』をアクティブにした状態でもRangeプロパティは『Sheet1』が指定されているので、『Sheet1』を見に行くのですが、Rangeプロパティ内のCellやColumns、Rowsはアクティブシートである『Sheet2』を見に行くことになるため、そこで「実行時エラー 1004」が起きてしまいます。

ちなにみ、『Sheet1』をアクティブにした状態で「実行時エラー 1004」が起きたVBA（test2）を実行しても「実行時エラー 1004」は起きません。

また、「実行時エラー 1004」が起きたVBA（test2）を『Sheet1』のシートモジュールに書いた場合もエラーは起きません。

スポンサードリンク

「実行時エラー 1004」の解決法の補足

Withステートメントを使ってtest3のVBAを書き直す事もできますよ。

Public Sub test4()
    Dim i As Long
    Dim j As Long
    Dim k As Long

    i = 10
    j = 6
    k = 15

    With ThisWorkbook.Worksheets("Sheet1")
        .Range(.Cells(1, 1), .Cells(i, 3)).ClearContents 'A1～C10の範囲:Cells使用
        .Range(.Columns(4), .Columns(j)).ClearContents 'D～F列すべて
        .Range(.Rows(11), .Rows(k)).ClearContents '11行目から15行目
    End With
End Sub

Rangeプロパティ内にあるCellやColumns、Rowsの前に「．（ドット）」がポイントです。

シート指定を1つで行るので管理はし易いかと思います。