ひよこさまのExcel講座

意外と知らないExcelの情報を書きます。
たまにExcel以外のことも書いています。

姉妹サイトVBA性能向上委員会も併せてどうぞ。
VBA性能向上委員会
 ⇒ http://www.banana-juice.com/VBA/

セルの書式を変えずにReplace

Replaceメソッドを使うと
実行後のフォントは、すべて一文字目のフォントになってしまいます。

という内容の記事を以前書いたのですが、今回はその解決方法をひとつ

以前の記事は以下リンクよりご覧ください。
ひよこさまのExcel講座:Replaceメソッドとフォントカラー


解決方法は、ずばり『置換前の書式を保持しておく』というもの。

サンプルコードを書いてみたので、載せます。

'╂━─━─━─━─━─━─━─━─━─━─━─━─━─━╂
'┃■書式を保持したままReplace
'┃
'┃@param schStr 検索文字列
'┃@param repStr 置換文字列
'╂━─━─━─━─━─━─━─━─━─━─━─━─━─━╂

Sub 書式を保持したままReplace(ByVal schStr As String, ByVal repStr As String)
Dim schCell As Range ' 検索セル
Dim preCell As Range ' 検索セル保持用
Dim i As Long ' セル文字列添え字
Dim strLength As Long ' セル文字列長

' セルを検索する
Set schCell = Sheet2.UsedRange.Find(What:=schStr, LookAt:=xlPart, MatchCase:=False, MatchByte:=False)

' 書式を保持するためのセルを設定(※別シートなど影響のないセルにする)
Set preCell = Sheet1.Range("A1")

Do Until schCell Is Nothing
' 元の書式を保持する
schCell.Copy preCell

' 置換実行
schCell.Replace What:=schStr, Replacement:=repStr, LookAt:=xlPart, MatchCase:=False, MatchByte:=False

' 文字列の長さを取得
strLength = Len(schCell.Value)

' フォントを元に戻す
For i = 1 To strLength Step 1
' フォントカラー
schCell.Characters(i, 1).Font.ColorIndex = preCell.Characters(i, 1).Font.ColorIndex
' schCell.Characters(i, 1).Font.Color = preCell.Characters(i, 1).Font.Color

' 太字
schCell.Characters(i, 1).Font.Bold = preCell.Characters(i, 1).Font.Bold

' フォント
schCell.Characters(i, 1).Font.FontStyle = preCell.Characters(i, 1).Font.FontStyle

' 斜体
schCell.Characters(i, 1).Font.Italic = preCell.Characters(i, 1).Font.Italic

' サイズ
schCell.Characters(i, 1).Font.Size = preCell.Characters(i, 1).Font.Size

' 下線
schCell.Characters(i, 1).Font.Underline = preCell.Characters(i, 1).Font.Underline

' 影
schCell.Characters(i, 1).Font.Shadow = preCell.Characters(i, 1).Font.Shadow

' 取り消し線
schCell.Characters(i, 1).Font.Strikethrough = preCell.Characters(i, 1).Font.Strikethrough

' 上付き
schCell.Characters(i, 1).Font.Subscript = preCell.Characters(i, 1).Font.Subscript

' 下付き
schCell.Characters(i, 1).Font.Superscript = preCell.Characters(i, 1).Font.Superscript
Next i

' 次のセルへ
Set schCell = Sheet2.Cells.FindNext(schCell)

' 書式を保持していたセルをクリア
preCell.Clear
Loop
Set preCell = Nothing
End Sub



このプログラムを
Call 書式を保持したままReplace("青", "緑")

こんな感じで実行すると、以下の画像のようになります。

replace3

「青」という文字が…



replace4

「緑」に置換されます!!



ただし、このプログラムはちょっといけてないところがあって、
検索文字列と置換文字列の長さが違う場合はうまく置換できません

Call 書式を保持したままReplace("黄, "きいろ")

こんな感じ。

replace5

なんということでしょう。
書式がずれてしまいました。



これを解決させるためには、文字列の長さの差分をみる必要があります。

ただし、それをするにはある程度制約がかかることを知っておいてください。

以下のケース。つらいです。(よーく考えて。)


うえ

これを、『ひらがな』に置換するケース。

置換後の『ひらがな』の色はどうしましょう?





個人的な考えだと、実はそんなことは気にしません。

そもそもExcelって『表計算ソフト』ですよね。
そんなWordみたいなセルの入力の仕方は、論理的でない気がするのでしないです。

Excelの入力の仕方は、以下であるべきだと思ってます。

replace6




そうすれば、いちいち書式を保持しておいて…とかしなくて済みますね

なるべくならめんどくさいロジックを書かなくてすむような書類にしておきたいものです。

特定の文字列を含む行を削除

先日、『特定の文字列を含む行を削除するマクロ』を作ってほしいという依頼を受けました。

ぱっと思いつく限りでは、こんな感じのソースが思いついたのですが…。

Sub 特定の文字列を含む行を削除()
    Dim c As Range
    Dim myRow As Long

    With Range("A:A")

        Set c = .Find("特定の文字列")

        Do While Not c Is Nothing
            Rows(c.Row).Delete shift:=xlUp

            Set c = .Find("特定の文字列")
        Loop
    End With
End Sub


ただ、このケースはマクロを組む必要はありません。
もっと楽な方法があるので、ご紹介します。

もちろん、依頼者にもこれから示す方法を教えてあげました。




以下のような表があるとします。

この表につき、『色列が、赤である行を削除する』とします。

図1


この場合、オートフィルタを使うと、『特定の文字列を含む行を削除』ことが簡単に実現できます。

まず、先頭行(タイトル行)にオートフィルタを設定し、色列を赤で絞り込みます。


図2


次に、絞り込まれた行を選択し、行の削除をします。

図3


最後にオートフィルタを解除すると、完了です。

『特定の文字列を含む行を削除』できました!

図4




オートフィルタを使いこなすと、『特定の行』に対して何かすることが非常に楽になります。
今回の例では、完全一致である行を操作していますが、オートフィルタの絞込み条件には、前方一致や後方一致、数値の比較などさまざまな条件を指定できます。

この様に、マクロを使わずとも、EXCELの機能を使ってルーチンワークをすることは可能なのです。

VBAだけでなくEXCELの機能についてもいろいろ知る必要がありそうですね。

セルの検索

セルの検索方法についてのページをアップしました。


検索といえばやはり『Findメソッド』ですね。
新しいメソッドが公開されるか、誰かが自前でそれを超えるメソッドを作らない限り、王道でしょう。


今回の検証では、1次元配列と2次元配列を検索対象としてみました。

ひとつ気になったのが、Findメソッドって、
2次元配列での検索の方が早いんですね。

もともとRangeオブジェクトは2次元配列なので、他の方法と比べると速いのは納得がいきます。

1次元配列を指定したとしても、内部的には、Cells(i, 1)のはずだし。

んー

これはちょっとわからないですね。
調べてみる価値はありそうです。


☆;+;。・゚・。;+;☆;+;。・゚・。;+;☆;+;。・゚・。;+;☆;+;。・゚・。;+;☆;+;。・゚・。;+;☆;+;。・゚・。;+;

今回アップしたページは以下のリンクから飛べます。

VBA性能向上委員会 - セルの検索(1次元配列)
VBA性能向上委員会 - セルの検索(2次元配列)


VBAプログラムの性能を上げるためのサイトがこちら。

VBA性能向上委員会
PR
記事検索
月別アーカイブ
livedoor プロフィール
カテゴリ別アーカイブ
QRコード
QRコード
  • ライブドアブログ