マクロの実行に時間がかかってしまう

質問

Excel2013です。
E列に従業員名、F列に部署名が入る表でVBAで従業員名が入力されると該当する部署名が表示されるようにしたく以下のようにコード（まだ一人分のIFステートです）を書いたのですが、すごく時間がかかってしまいます。
はじめは、実行にボタン登録したくなかったのでchangeイベントで実行するようにしたのですが、何度やってもExcelが落ちてしまい、今はボタンにマクロを登録させています。落ちることはなくなったのですが、ずっと砂時計です。
勤怠記録なので数十名分（一人二行）で365日の表になるので最終行は2万近くです。

Sub 部署名判断()

Dim a As Long

Application.ScreenUpdating = False

For a = 1 To 16000 '部署名判断
If Cells(a, 5).Value = "従業員A" Then
Cells(a, 6) = "総務課"
End If
Next a

Application.ScreenUpdating = True

End Sub

どういったコードの書き方をすればいいのでしょうか？
アドバイス願います。

回答No.2

tom04

こんにちは！

すべての行をループさせていますので、時間を要するのかもしれませんね。
オートフィルタを使ってみてはどうでしょうか？

通常フィルタをかける場合、項目行が必要ですが、コードを拝見すると1行目からループさせていますので、
データは1行目からあるとします。

Sub Sample1()
Dim lastRow As Long
Application.ScreenUpdating = False
Rows(1).Insert
Range("E1") = "ダミー"
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
Range(Cells(1, "E"), Cells(lastRow, "E")).AutoFilter field:=1, Criteria1:="従業員A"
Range(Cells(2, "F"), Cells(lastRow, "F")).SpecialCells(xlCellTypeVisible) = "総務課"
ActiveSheet.AutoFilterMode = False
Rows(1).Delete
Application.ScreenUpdating = True
End Sub

少しは短縮できるでしょうか？m(_ _)m

お礼

ご回答ありがとうございます。

いただいたコードを試してみました。数秒間かかりますが確かに格段に早くなりました。

他のシートの関数も生かしたままマクロを動かしたいのでしばらく、このやり方を採用してみようと思います。

他にもアドバイスのある方がいらっしゃればまだ、投稿は閉じませんので宜しくお願いします。

回答No.1

kybo

For a = 1 To 16000 '部署名判断

Next a

で16000行すべてチェックするのは確かに時間がかかりますが、
こちらの環境では、1秒以内で終わりました。

なので、コードよりも、表の方に何か問題があるのかもしれません。

試しに、新規ブックに、Ｄ列のみにデータを値のみ入力（書式はコピーはせず、印刷範囲もしていない）して、どのような結果になるのか確かめるとよいかもしれません。

お礼

ご回答ありがとうございます。

私も新規ブックで行ってみましたが、一瞬で終わりました。
問題のブックでは他にもシートがありそれらのシートではSUMIFSやROUND関数が使われてきて問題のシートのセルを参照しています。

試しに問題のブックでマクロが関係してこないシートを削除した後マクロを実行するとやはり一瞬で終わってくれるのでそれらのシートが原因のようなのですが、そんなに複雑な関数を使っているわけでもないので不思議でたまりません。Excelのオプションの計算の設定かなにかで悪いところでもあるのでしょうか？