[[20240604161524]]　『置換ちかんチカン』（明治の漢）　ページの最後に飛ぶ

『置換ちかんチカン』（明治の漢）

指定範囲(列と開始行)を選択して
文字と数値の置換を行いたい

文字の場合は「削除」⇒「」←ブランク
文字の場合は「#N/A」⇒「」←ブランク
数値の場合は「0」⇒「」←ブランク
数値の場合は「10」⇒「10」

数字の場合は完全一致にて行いのですが

マクロの記録で行うと下記の様になるのですが

    Selection.Replace What:="#N/A", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
    Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

処理がものすごく遅くなります

これをもっと早い処理で行う方法はないでしょうか
また、列や行の変更を関単に行える様な式ですと大変助かります

よろしくお願いします。

＜使用 Excel：Microsoft365、使用 OS：Windows10 ＞

 ＞処理がものすごく遅くなります
 何秒くらい掛かるのですか？

 ＞これをもっと早い処理で行う方法はないでしょうか
 画面更新を一時停止してみたらどうですか？

 冒頭に：   Application.ScreenUpdating = False
 末尾に：   Application.ScreenUpdating = True

 ＞列や行の変更を関単に行える様な式
 具体的にどんな変更を想定していますか？(脳内で念じてもエクセルには伝わりませんけど）

（半平太） 2024/06/04(火) 20:14:44

 Excelのネイティブな置換機能ですから、懸念されるほど遅いことは余りないように思いました。

 Selection.Addessは例えばどのようなものになっているんですか？

 また、そのシートに大量の数式などが入っている場合には、
 計算モードをいったん手動に変更して、処理終了後自動に戻す などの方策も有効かもしれません。

 なお、質問の際は、コードを省略せずに、Sub XXX() から End Subまで示し、
   Dim t
   t = Timer 
   (ここに処理コード)
   Debug.Print Timer - t
 などとして、実際にかかる時間を示すとよかったでしょう。

（xyz） 2024/06/05(水) 07:51:50

こういうこと？

Option Explicit

Sub test()

    Dim Rng As Range
    Dim v As Variant

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    With Selection
        Set Rng = Application.Range( _
                  .Cells, .Cells(.Worksheet.Rows.Count, .Column).End(xlUp))
    End With

    For Each v In Array("削除", "#N/A", "0")
        Rng.Replace What:=v, _
                    Replacement:="", _
                    LookAt:=xlWhole, _
                    SearchOrder:=xlByColumns, _
                    MatchCase:=False, _
                    SearchFormat:=False, _
                    ReplaceFormat:=False, _
                    FormulaVersion:=xlReplaceFormula2
    Next

    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
End Sub

そんなに、提示のコードですごーく遅いなら、
手動でやっても遅いですか？
なんにしても「速く」よりも、
「間違いなく確実に楽にできる」を主眼においてみては？

参考URL>>
https://www.sejuku.net/blog/69323
http://officetanaka.net/excel/vba/speed/
（まっつわん） 2024/06/05(水) 09:26:25

まっつわんさんありがとうございます
Set Rng = Application.Range(.Cells, .Cells(.Worksheet.Rows.Count, .Column).End(xlUp))
ここの部分でオブジェクト定義エラーになってしまいます。

よろしくお願いします。
（明治の漢） 2024/06/05(水) 10:40:08

すみませんでした。
修正しました。
セルの位置の指定はややこしいですね＾＾；

Option Explicit

Sub test()

    Dim Rng As Range
    Dim v As Variant
    Dim s As String

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With

    If TypeName(Selection) = "Range" Then
        With Selection
            Set Rng = .Worksheet.Cells(.Worksheet.Rows.Count, .Column).End(xlUp)
            If .Row <= Rng.Row Then
                Set Rng = Application.Range(.Cells, Rng)
            Else
                Set Rng = Nothing
            End If
        End With

        If Not Rng Is Nothing Then
            For Each v In Array("削除", "#N/A", "0")
                Rng.Replace What:=v, _
                            Replacement:="", _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByColumns, _
                            MatchCase:=False, _
                            SearchFormat:=False, _
                            ReplaceFormat:=False
            Next
        End If
        s = "置き換え完了"
    Else
        s = "セルが選択されていません。"
    End If

    With Application
        .Calculation = xlCalculationAutomatic
        .ScreenUpdating = True
    End With
    MsgBox s
End Sub

（まっつわん） 2024/06/05(水) 11:54:28

まっつわんさん、ありがとうございます
自分で考えろって言われたらって思ってびくびくしてました…
これ最高です！
今後も使わせてもらいます！
処理も早いので助かります(^_^)/~
（明治の漢） 2024/06/05(水) 12:29:44

 結果オーライですか？

 遅かったという時のSelectionは何だったのですか？
 ・予想を超えて大きな範囲だったことが原因だったのですか？
 ・画面更新を抑止したら早くなったのですか？
 ・それとも、そのシートには数式がたくさんあったのですか？計算モード変更が効果あったのですか？

（xyz） 2024/06/05(水) 14:08:54

Selectionはその前にコピー値貼付をする動作をしてたので都合が良いのでそのまま使用してました

画面更新を止めてても結果は変わらず遅いままでした

列数は、117列あり行数は1000行ほどです

（明治の漢） 2024/06/05(水) 15:30:18

 コメントありがとうございました。

 Selectionの設定は通常の範囲のものだったのですね？
 間違って、膨大な範囲を指定していたということは無いのですね？
 置換されるセルを参照して計算する式が結構な数に上っていたということですか？
 であれば手動に変えた効果ということになりますかね。

（xyz） 2024/06/05(水) 15:43:02

そうですね、間違って膨大な範囲を指定していたって事は無いですね
そもそも、膨大な範囲ではありますが(;^_^)

（明治の漢） 2024/06/06(木) 09:23:58

計算式の多寡はどうでしょうか。
（xyz） 2024/06/06(木) 09:27:24

まっつわんさんの回答のコードの最初の下記の部分を削除した場合にどうなるかもお聞きしたいですね。

    With Application
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
    End With
（hatena） 2024/06/06(木) 10:22:43

コメント返信：

[ 一覧(最新更新順) ]

[[20240604161524]] 『置換ちかんチカン』（明治の漢） ページの最後に飛ぶ

[[20240604161524]]　『置換ちかんチカン』（明治の漢）　ページの最後に飛ぶ