Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
空白セルに記号挿入
投稿日時: 21/02/11 01:13:08
投稿者: 0110AH

まだまだ勉強し始めたばかりで,期待通りに動いてはくれますが,
約5000行のデーターで1分近く時間がかかります.
きっと書き方が悪いのだと思います.
よろしくお願いします.
 
A列の最終行を取得して,
G列とI列で空白セルがあったら,半角アンダーバーを入れる処理です.
 
sub test()
 
Dim LastRow, i, y As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
 
i = 1
For i = 1 To LastRow
If Cells(i, 7) = "" Then
Cells(i, 7) = "_"
End If
Next i
 
y = 1
For y = 1 To LastRow
If Cells(y, 9) = "" Then
Cells(y, 9) = "_"
End If
Next y
 
End Sub

回答
投稿日時: 21/02/11 06:13:47
投稿者: simple

■5000行としても1分は掛かりすぎですね。
たぶん、数式がたくさんあるシートなので、書き込みの都度、再計算が走ることが
原因と想像されます。
 
プロシージャーの最初で、

   Application.Calculation = xlCalculationManual
と手動計算モードにして、
最後に、
    Application.Calculation = xlCalculationAutomatic
と自動計算モードにするコードを挿入することで、大分改善するはずです。
(下記を実行する前に、是非試してみてください。結果を教えて下さい)
 
■現在のコードは1セルごとに書き込みをしていますので、その分、時間がかかります。
配列にしておいて一括して書き込むと、速度向上が望めます。
これは結構効果のある定石になっています。セルが多ければ多いほど、効果は大きくなります。
 
こんな形式のコードです。
Sub test2()
    Dim LastRow As Long, j As Long, k As Long
    Dim v

    Dim t
    t = Timer
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For k = 7 To 9 Step 2
        v = Cells(1, k).Resize(LastRow, 1) '配列に取り込む
        For j = 1 To LastRow
            If v(j, 1) = "" Then
                v(j, 1) = "_"
            End If
        Next
        Cells(1, k).Resize(LastRow, 1) = v '一括して書き込む
    Next
    Debug.Print Timer - t
End Sub
イミディエイトウインドウに所要時間(秒単位)が表示されるので、確認して下さい。
 
■貴兄の掲示コードで気づいたその他の点。
1. Dim LastRow, i, y As Long とすると、Longなのは yだけで、他はVariant型です。
   ひとつひとつ型を指定する必要があります。(上記コード参照)
 
2. i = 1
   For i = 1 To LastRow
   としていますが、 i = 1と書く必要はありません。
 
3.インデントをキチンと付けた方が、あなたにとってメリットがあります。
   コードの構造が見やすくなるはずです。今後もっと複雑なコードになったときに
   今のままだと立ち往生するはずです。
 
4.これは趣味の領域の話です。iとかyなどの繰り返しのカウンタ変数は、
   伝統的に、i,j,k,l,m,n などが使われることが多いです。
   i は l や 1と誤認しやすいので、j,k などが多用されると思います。
   変数は自由に使えますが、ある程度、予測可能性があるものが便利です。

回答
投稿日時: 21/02/11 21:40:14
投稿者: simple

ああ、後半の配列利用の方法は、式がその範囲にあると値になってしまう点に注意が必要です。
入力値という雰囲気なので、式はないかもしれませんが。
 
このほか、「空白セル」に「ジャンプ」して、Ctrl+Enterキーを押して"_"を入力する
と言った方法もあるでしょう。マクロ化も、記録を取れば簡単にできます。

回答
投稿日時: 21/02/12 07:26:14
投稿者: simple

念のため、「空白セル」に「ジャンプ」する方法のコードも上げておきましょう。
これが一番速かったですね。
 

Sub test3()
    Dim LastRow As Long
    Dim k As Long
    Dim rng As Range

    Application.Calculation = xlCalculationManual '
    
    LastRow = Cells(Rows.Count, 1).End(xlUp).Row
    For k = 7 To 9 Step 2
        Set rng = Cells(1, k).Resize(LastRow, 1).SpecialCells(xlCellTypeBlanks)
        rng.Value = "_"
    Next
    
    Application.Calculation = xlCalculationAutomatic
End Sub

投稿日時: 21/02/12 14:46:11
投稿者: 0110AH

simpleさん
 
わかりやすくご説明くださいましてどうもありがとうございました.
また,コードの書き方のご指摘とても嬉しいです.
 
まずはプロシージャーの最初と最後に挿入した以下の報告です.
   Application.Calculation = xlCalculationManual
   Application.Calculation = xlCalculationAutomatic
 
ストップウィッチでの測定で2秒でした.
嘘の様です.
 
1セルごとの処理ではなく,配列にして一括での処理のお話.ありがとうございます.
ご提示いただきましたコードを少し時間をかけて拝見します.
 
最後に,一番早い.と仰ったコードを実行してみました.
一瞬でした!
 
本当にありがとうございました.