[[20120919182209]]　『VBA　複数の非連続範囲の取得方法』（usamiyu）　ページの最後に飛ぶ

『VBA　複数の非連続範囲の取得方法』（usamiyu）

 いつも大変お世話になっております。
初心者ですが、次のことを行いたく、間違いを繰り返しているのですが、
どなたか、お手すきの時にお力をお貸しいただけますか。

 (概  要）
C,U,V列の8行目からデータのある行までに入っている半角文字を
全角に変換したいのですが非連続列にあるデータの最終行までの取得が
できません。
自分で書いたコードは以下に記載したものですが、どのように修正すれば
よいものか教えてください。

 （レイアウト）
　　　　C列　 　　　 U列 　　　　　  V列

 ８行目　機器A-00　　　B株式会社　　　C1式(D含む)
　・
　・
 最終行　機器E-00　　　F株式会社　　　G1式(H含む)
　
 これを↓のように全角に変換（カッコも全角に）

 ８行目　機器Ａ－００　Ｂ株式会社　　Ｃ１式（Ｄ含む）
　・
　・
 最終行　機器Ｅ－００　Ｆ株式会社　　Ｇ１式（Ｈ含む）

 （コード）
 Sub hannkaku_zennkaku()
    Dim r As Range
    Dim sh As Worksheet

     Set sh = Sheets("F551")

     For Each r In Range("c8:c200,u8:u200,v8:v200")'ここを仮に200行目とするとなんとかできました。
        r = StrConv(r, vbWide)
     Next

 End Sub

 仮置きした200行目でなく、データの最終行までの取得としたいのですが、
 "c8",.Range("C" & Rows.Count).End(xlUp).row　とか？使ってみましたが、
３列のくっつけ方が悪いのか、コンパイルエラーになったりします。
 Rangeの中を変数にしてみるというのも考えたりしましたが、rangeメソッド
の中には変数は使えないのですよね。

 初歩的な質問で恐縮ですが、どうぞよろしくお願いいたします。

こんな感じで動きましたよ。

For Each r In Range("c8:c" & Range("C" & Rows.Count).End(xlUp).Row & ",u8:u200,v8:v200")

（ドカ）

 （ドカ）さん　ありがとうございます。
 ご回答のうち、",u8:u200,v8:v200")の部分は、c列と同様に修正するということですよね？

 次のようにつなげてみましたが、実行時エラー１００４となります。
 カンマか何かが足りないのでしょうか？

 Range("c8:c" & Range("C" & Rows.Count).End(xlUp).Row & "u8:u" & Range("u" & Rows.Count).End(xlUp).Row & "v8:v" & Range("v" & Rows.Count).End(xlUp).Row)

 (usamiyu)

 > カンマか何かが足りないのでしょうか？
 その通りですね。

 アドレスの部分を、
    MsgBox "c8:c" & Range("C" & Rows.Count).End(xlUp).Row & "u8:u" & Range("u" & Rows.Count).End(xlUp).Row & "v8:v" & Range("v" & Rows.Count).End(xlUp).Row
 として確認したらすぐに分かると思います。

    MsgBox "c8:c" & Range("C" & Rows.Count).End(xlUp).Row & ",u8:u" & Range("u" & Rows.Count).End(xlUp).Row & ",v8:v" & Range("v" & Rows.Count).End(xlUp).Row
 と見比べてみてください。

 セルの指定方法はたくさんあると思いますが、私がこの処理を書くとしたら、
    For Each c In Array("C", "U", "V")
        For Each r In Range(Cells(8, c), Cells(Rows.Count, c).End(xlUp))
            r.Value = StrConv(r.Value, vbWide)
        Next
    Next
 のような形にするかもしれません。

 けれどデータが必ず8行目以降にないと、期待しない動作になると思います。
 そのような場合があるのであれば、別途対策が必要ですけれど。
 ご参考までに。
 (Mook)

Unionを使う方法もあります（マナ）

 Sub test()
    Dim r As Long
    Dim r1 As Range, r2 As Range, r3 As Range
    Dim c As Range

    r = Range("A" & Rows.Count).End(xlUp).Row

    Set r1 = Range("A1:A" & r)
    Set r2 = Range("C1:C" & r)
    Set r3 = Range("E1:E" & r)

    For Each c In Union(r1, r2, r3)
        c.Value = StrConv(c.Value, vbWide)
    Next

 End Sub

  (Mook)さん　先日に引き続き、ありがとうございます。
 また、お話する機会をいただけて、大変うれしいです(^^)
 実は、こんなことで２週間ぐらい、あれこれ悩んでいました。なさけないです。

 MsgBox、早速、比較確認しました。よくわかりました。カンマがないと、
三つのかたまりごとにならないんですね。MsgBoxを確認に使うってわかりやすくて
いいですね。
 それと、r =じゃなくて、r.Value =とするべきだったんですね。反省です。

 (Mook)さんの処理方法、VBEに書き込んで試してみました。鮮やかで美しいです！素敵です♪
それで、2点ほど質問があるのですが、もし、よろしければ教えてください。

 1　対象ブックにシートが複数枚ある場合は、シートを指定すればよいですか？
　　自分で記載したものでは、変数shに対象シートを代入しましたが、他によりよい方法はありますか？

 2  >データが必ず8行目以降にないと、期待しない動作になる
　　必ずありますが、もしない場合は、.End(xlUp)ではだめということですよね。
　　えーと、最終行から、空白行のあるところまでデータをカウントしてくるので、8行目以降に必ずないと・・・
    ということですか？もし8行目以降に空白行があって、一つ飛ばして、次の行にデータがある場合も別の
    方法をとる必要がありますか？

 (usamiyu)

 （マナ）さん　はじめまして。ありがとうございます。
 いろいろ試行錯誤していたときにRangeの中を変数にしてみるというのを考えて、
やっぱりだめだと思っていましたが、中じゃなく、こんな方法もあるのですね。
勉強になりました。Union、自分でも使ってみたいです。

  r = Range("A" & Rows.Count).End(xlUp).Row
 とすると、A列の行数分の範囲ということですね。
 もし、各列の行数が違って、空欄があったりすると、気をつけなければならない
ということですね。でも、その場合は、ｒを一つでなく、ｒ以外の変数を設定すれ
ば、できそうですね。

 (usamiyu)

 とりあえず、ご質問への回答です。

 > 1　対象ブックにシートが複数枚ある場合は、シートを指定すればよいですか？

 指定しない場合はアクティブなシートが対象となりますので、複数シートを処理したい
 場合はそのようになります。

 >　自分で記載したものでは、変数shに対象シートを代入しましたが、他によりよい方法はありますか？

 これはケースバイケースなので一概には言えませんが、全部のシートを処理するのであれば

 For Each ws In WorkSheets
    For Each c In Array("C", "U", "V")
        For Each r In ws.Range(ws.Cells(8, c), ws.Cells(Rows.Count, c).End(xlUp))
            r.Value = StrConv(r.Value, vbWide)
        Next
    Next
 Next
 のようにもできますし、特定の数シートだけなら、個々に Set した方が早いかもしれません。
 シート名に何か条件がある、例えば「データ」という文字のあるシートだけ、であれば、

 For Each ws In WorkSheets
    If InStr( ws.Name,"データ") > 0 Then
        For Each c In Array("C", "U", "V")
            For Each r In ws.Range(ws.Cells(8, c), ws.Cells(Rows.Count, c).End(xlUp))
                r.Value = StrConv(r.Value, vbWide)
            Next
        Next
    End If
 Next
 というようにもできます。

 > 2  >データが必ず8行目以降にないと、期待しない動作になる
 >　　必ずありますが、もしない場合は、.End(xlUp)ではだめということですよね。
 >　　えーと、最終行から、空白行のあるところまでデータをカウントしてくるので、8行目以降に必ずないと・・・
 >    ということですか？もし8行目以降に空白行があって、一つ飛ばして、次の行にデータがある場合も別の
 >    方法をとる必要がありますか？

 8行目以降、途中に空白がある場合は問題ないですが、例えばU列が5行までしかないとしても、
 U5 が処理されてしまいます。
 こういうケースをどう排除するか、というのは意外と難しいのですが、データが少なくとも
 8行目にはあるのであれば、今回は気にしなくともよいです。
 (Mook)

  (Mook)さん　わぁ、こんなにくわしく教えていただけるなんて、幸せです！
 お言葉のひとつひとつ大事に理解していきます。
 for each構文をネストしていくといろいろできるのですねぇ。今後はこんなふうに
 使ってみたいです。

 If InStr( ws.Name,"データ") > 0 Then　っていうのも、面白いですね。
 InStrで、"データ"が見つかった文字位置を数値で返して、それがあったら、次の
 for eachで処理するんですね。

 2番目の件については難しくて、理解が不十分ですが、.End(xlUp)とか(xldown)とか
もう少し勉強してみます。

 初心者にお付き合いいただいて、本当にありがとうございました。
 お話しできてすごく楽しかったです(^^)
 また、次の機会にもぜひご指導いただけたらうれしいです。

 (usamiyu)

 xlUP と xlDown は Ctl キーと ↑、↓ を押したときの動作と私は認識しています。
 ですから、あるセルからどこまでカーソルが移動するのか、
 データが連続している場合、データがない場合、飛び飛びである場合などで
 試してみると動作がわかるかと思います。
 基本的にはデータの切れ目だと思うのですが、データの最後や途中、空白列などで
 特異な動きをするのでそのあたりを理解しておくとよいかと思います。

 楽しみながらマクロを覚えてください。
 (Mook)

コメント返信：

[ 一覧(最新更新順) ]

[[20120919182209]] 『VBA 複数の非連続範囲の取得方法』（usamiyu） ページの最後に飛ぶ

[[20120919182209]]　『VBA　複数の非連続範囲の取得方法』（usamiyu）　ページの最後に飛ぶ