[[20031027191229]]　『VBA：最終列の取得』（ケン）　ページの最後に飛ぶ

『VBA：最終列の取得』（ケン）

最終行の取得はCells(Application.Rows.Count, "A").End(xlUp).Rowで取得したのですが1行の最終列の取得方法が分かりません。教えてください。

ぼくは、Range("IV2").End(XltoLeft).column でやっています。Range("IV" & GyoNumber) でももちろんやれます。

たては、Range("A65536").End(XlUp).row でやっています。（通りすがりの者）

  ケンさんって、私のライバルのケンさんでっしゃろ。
 色んな方法がおますけど、もっぱら私が使うとるんは、
 Colno=Cells(1,Columns.Count).End(xlToLeft).Columnとか
 Do loop で後から数えるとか
 不特定の最終列やったら、
 ColNo = Worksheets("sheet1").UsedRange.Columns.Count
 ただdo loop　以外の方法やと=if(a1="","",a1)と言った案配の式が入っとってそれが
 ""の状態でも拾うて、そのセルが最終列になります。'も同じだと思います。
 もっとええ方法が有るんかも知れまへんけど、もうチョッと待っとったら教えてくれま
 っしゃろ。ほ～れ早速カナン師匠が教えてくれはった、な。
 　　（おいぼれ　弥太郎）
 小切手届いた？

ご質問は、特定行、特定列の尻ではなく、テーブルの尻でしたか。その場合は、ぼくは次のようにしています。（A1 から始まっているとして）（通りすがりの者）

 range("A1").CurrentRegion.Columns.Count と
 range("A1").CurrentRegion.Rows.Count でやっています。

 阪神負けていますなぁ…。

 衝突してしまいましたので、通りすがりの者さんの↑は検証してませんが
 通りすがりの者さん、弥太郎さん有難う御座います。ライバル？違いますよ、舎弟のケンです。
ところで今、私が作っていたのは
    A    B    C    D    E
 1  a    v    s    q    l
 2  d    w    r    j    o
 3  e    h    t    u    p
と有ったとしてｒはどこに有るか検索するマクロを作っていました。but
A列と１行の最大の範囲でしか検索出来ないダメダメマクロです。
    A    B    C    D    E   F
 1  a    v    s    q    l
 2  d    w    r    j    o   m
 3  e    h    t    u    p
 4       y
こんな感じの時は、どうしたら良いのでしょうか？ちなみに、そのダメダメマクロは、
 Sub kensaku()
    Dim i As Long, LastR As Long, LastC As Long, c As Long
    Dim X As String, addr As String
        LastR = Range("A65536").End(xlUp).Row
        LastC = Range("IV1").End(xlToLeft).Column
        X = Application.InputBox("検索する文字は？")
        If X = False Then Exit Sub
        For c = 1 To LastC
        For i = 1 To LastR
            If Cells(i, c).Value = X Then
            addr = Cells(i, c).Address
            MsgBox addr & "です"
            Exit Sub
            End If
        Next i
        Next c
        MsgBox "ヒットしませんでした"
 End Sub
通りすがりの者さんのお知恵をお借りして、こうしてみました。よろしくお願いします。
あっ！小切手は、のしを付けて弥太郎さんに送りました。（ケン）

 >阪神負けていますなぁクククッ。（...を拡大してみたらこうなった。）
 なんやそれっ。喜んどんかいなっ！
 ほっといてんかっ！

 >小切手は、のしを付けて弥太郎さんに送りました
 え～っ、まあもったいない。あれには私の血と汗の結晶が数字となって刻み込んである
 し、なによりも、jun太郎さんのサイン入りの小切手でっせ。
 はは～ん、彼の出世はあり得んと踏んどるわけだんな。マクロ仲間やいうのに冷たい御
 方。
 きょうは、また昨日にも増して気分が優れまへんわ。
 マクロでっか。テーブルやったらカナン師匠のCarrentRegion で求めるべきですわ。
 このマクロの欠点はA列の最下行、１行目の最右行しか求められてまへんし、他にデー
 タがあるとその行迄作業してまいますわ。
 他にデータがないんやったらusedrangeで求めるんも方法です。
        （もう絶望の　弥太郎）

 今日はよく衝突するな～。弥太郎さん嫌がらせしてるの？（笑）
 通りすがりの者さんの教えてくださった物で完璧でした。有難うございます。
ですがInputBoxのキャンセルボタンを押した時の処理が、よく分かりません。もう一度教えてください。
m(_ _)m （ケン）

弥太郎氏はあんなことを言われていましたが、本当は阪神が負けてショックで出て来られないようなので、InputBox の件、代理で書きます。InputBox はキャンセルボタンがクリックされると、Boolean を返すのではなくて、単にヌル "" を返します。ですから、If X="" then .... となります。

なお、もちろんこれはご存知でしょうが、InputBox("プロンプト","ウインドタイトル（上端の青帯部分）","入力内容既定値") となります。（通りすがりの者）

 通りすがりの者さん有難う御座います。だれも回答してくれないので、ふてくされて寝ようかと
思っている所に、神の手が差し伸べられたような気がしました。しかしキャンセルボタンを押すと
Exit Subしてくれず”ヒットしませんでした”のメッセージが返ってきます。なぜでしょう？
 Sub kensaku()
    Dim i As Long, LastR As Long, LastC As Long, c As Long
    Dim X As String, addr As String
        LastR = Range("A1").CurrentRegion.Rows.Count
        LastC = Range("A1").CurrentRegion.Columns.Count
        X = Application.InputBox("検索する文字は？")
        If X = "" Then Exit Sub
        For c = 1 To LastC
        For i = 1 To LastR
            If Cells(i, c).Value = X Then
            addr = Cells(i, c).Address
            MsgBox addr & "です"
            Exit Sub
            End If
        Next i
        Next c
        MsgBox "ヒットしませんでした"
 End Sub
今現在のコードです。何度もお手を煩わせますが、お願いします。（ケン）

 今使っているInputBoxは関数でなくメソッドです。重ね重ねm(_ _)m 
もちろん、見れば分かるでしょうが。（ケン）

いえ、分かりませんでした。ぼくは、すっかり、関数と勘違いしてました。メソッドの方は、キャンセルは boolean ですね。=False です。="" と違います。失礼しました。

でも、すると最初のでうまくいきませんか。いかないとすると、X を string で宣言しているからではありませんか。メソッドの方では、string として扱ったり、boolean として扱ったりしているのです。ですから、as Variant として宣言しなければいけないのではないでしょうか。（通りすがりの者）

 できました。(^_^)有難う御座います。変数の宣言がいまいち、よく分かっていません。
もっと勉強せねば。有難う御座いました。（ケン）

 いんや、勉強せんでもよろしい。
 つい先日まで足首捕まれとったんが、もう腰のベルトまで手ぇが伸びてきてまんがな。
 やがて真後ろに肩を並べて、ニカッと笑うたと思うまもなくワシの肩を踏み台にして
 ビヨ～ンと飛び上がってしまいまんねんで、きっと。
 想像するだけでも気分が悪うなってきますわ、ホンマに...。（笑）

 ところで、昨夜は放ったらかしとってゴメンやで。実は思いもよらん出来事の心痛で伏
 せっておったんですわ、えぇ。
 さぁほんなら気ぃ取り直して昨夜の補講といきまひょか。
 CurrentRegionは隣接したセルが有る限りどこまでもテーブルやと認識して拾い出して
 くれる利便性の高い賢～いメソッドですわ。
 反面、テーブル以外のデータが隣接しとったらそのデータまでテーブルとみなし、作業
 を続けてしまう危険性も孕んでおります。
 したがってこのメソッドを使うばやいは空白行列で完全に区切っておく事が絶対条件に
 なります。
 更に、ケンさんの標本の様にＡ１から始まるようなテーブルやと簡単ですけど、これが
 C5とかＨ10とかから始まるばやい（自分で表を見ながら組むんやったらどうっちゅう事
 おまへん）そのテーブルのどこかにセルポインターをセットするようユーザーにお願い
 するか、テーブルを捜し出すコードを組み込まなあきまへん。
 私が質問者に解答する時にはポインターをセットするようメッセージを流します。（こ
 の場合左端上端にセットして貰うとマクロが組み易い）

 最終行、最終列を求める方法はいくつかおますけど、それぞれ１長１短おますさかい、
 そこらは臨機応変にメソッドを使い分けなしょうおまへんなぁ。
 まぁ、今んとこアナタに講義でけるんはこんなことですわ、えぇ。
 それにしても初心者が初心者に講義するって烏滸がましすぎるんちゃうん？
 ほんじゃ１日も遅く腕前があがって来るんをお待ちてま。
 　　　ほな...（おいぼれ　弥太郎）

 こんにちは
 ケンさん、これで何を検索してるんでしょう、、、ん！
 私得意の他人様コード丸ごと横取りで。。　これ、私のにも使えますので
 数値検索にしてみました。ケンさん、通りすがりの者さん、弥太郎さん、ありがとうございます。

 一つ教えて頂きたいのですが
 [a1:z10000] の範囲検索で、
 初期化のところ、もう少しなんとかならないでしょうか？（参照形式もバラバラです）
 宜しくお願いします。　　　（jun53）

 Sub kensaku0()
    Dim i As Long, LastR As Long, LastC As Long, c As Long
    Dim X As String, addr As String

       Range("a:z").Select           '以下４行で前回の色付けと番地を初期化
       Selection.Interior.ColorIndex = xlNone
       Cells(1, 27).Select
       Selection.ClearContents

        LastR = Range("A1").CurrentRegion.Rows.Count
        LastC = Range("A1").CurrentRegion.Columns.Count
        X = Application.InputBox("検索する数値は？", "", "半角数字で入力して下さい。")
        If X = False Then Exit Sub
        For c = 1 To LastC
        For i = 1 To LastR
            If Cells(i, c).Value = X Then
            addr = Cells(i, c).Address
            Cells(1, 27) = addr & "です"            '特定セルに番地を出しました
            Cells(i, c).Interior.ColorIndex = 3      '検索番地に色付け、趣味良くないけどこの色です
            Exit Sub
            End If
        Next i
        Next c
        MsgBox "ヒットしませんでした"
 End Sub

 も一人のライバルさん、初期化の所
 range("a:z").clear　１行で事足りるんやおまへんか？
 お試し乞う。
 　　　　（空は晴れても心は闇　の弥太郎）

 私の大切な、億単位の一日の小遣いデータ迄消してしまうんですか？
 残念ながら　否決　です（笑）　　　（jun53）

 jun53さんも勉強熱心ですね。負けちゃ～いれねぇ～！
Range("a:z").Select ですが、せっかくLastR = Range("A1").CurrentRegion.Rows.Count
とLastC = Range("A1").CurrentRegion.Columns.Countでテーブルの範囲を取得しているので
Range(Cells(1, 1), Cells(LastR, LastC)).Selectとした方が良いと、素人の私なりに感じました。
ライバルのjun53さんへ　by(ケン）

 あ～そう言う意味やったんかいな？
 range("a:z").clearformats で万事ＯＫ
 　　相変わらず理解力乏しい　弥太郎

 >Cells(LastR, LastC)).Select　は
 LastR =
 LastC =
 の前なので？認識してくれませんね。
 それと、最終的には Z列以降にも数字が入る可能性があるので
 取得範囲も
 Range("A1").CurrentRegion.Columns.Count
 を修正して限定させる必要が有るんです。

 >勉強熱心ですね
 チョットこのファイルだけは本気になって取り組まねば、、なのです。
 今週いっぱいくらいですけどね。後はまたダラダラです。
 弥太郎さん、ClearFormats    書式までクリアされましたー。でもありがとうです。

 これから遅い昼食です。少しの間失礼します。　　　（jun53）

 Sub kensaku1()
    Dim i As Long, LastR As Long, LastC As Long, c As Long
    Dim addr As String
    Dim X As Variant
        LastR = Range("A1").CurrentRegion.Rows.Count
        LastC = Range("A1").CurrentRegion.Columns.Count

        Range(Cells(1, 1), Cells(LastR, LastC)).Select
        Selection.Interior.ColorIndex = xlNone
        Cells(1, 27).Select
        Selection.ClearContents

        X = Application.InputBox("検索する数値は？", "", "半角数字で入力して下さい。")
        If X = False Then Exit Sub
        For c = 1 To LastC
        For i = 1 To LastR
            If Cells(i, c).Value = X Then
            addr = Cells(i, c).Address
            Cells(1, 27) = addr & "です"
            Cells(i, c).Interior.ColorIndex = 3
            Exit Sub
            End If
        Next i
        Next c
        MsgBox "ヒットしませんでした"
 End Sub

として見ました。（ケン）

 面白そうなのでぼくも入れてください。（ken!）
 これ1件しか検索しないってことでいいんですか？
 エラー処理とか考えてませんけど、あとはがんがってくだちぃ。

 Sub SearchNumber()
    Dim adr As Object, c As Object
    Dim X As String

    Set adr = Range("A1").CurrentRegion
    adr.Interior.ColorIndex = xlNone
    Cells(1, 27).ClearContents

    X = Application.InputBox("検索する数値は？", "", "半角数字で入力して下さい。")
    If X = False Then Exit Sub
    For Each c In adr
        If c.Value = X Then
            Cells(1, 27) = c.Address(False, False) & "です"
            c.Interior.ColorIndex = 3
            Exit Sub
        End If
    Next c
    MsgBox "ヒットしませんでした"
 End Sub

 なんちゃって（ramrun）

 jun53さ～ん、またまたお宝コードが出てきましたよ～。
ramrunさん、有難う御座います。
遅撃ちマックの（ケン）

 弥太郎さん講義、有難う御座います。お礼が遅くなりました。m(_ _)m （ケン）

 初期化　なんて気取って書いたんで弥太郎さんを迷わせてしまいました、すみません。色つきだけ消したかったのです。
 ケンさん、ramrunさん、ありがとうございます。
 ramrunさんのコード、ずいぶんすっきりですねー。宣言とは、、？　理解してないんですね、私は。。
 Address に $ が付いてない？？　False と True の違いですか、知らない事が多すぎますね。
 結局、番地は上書きしてしまうので最初にクリアする事無かったですね。
 最終的には、初期化と検索のボタンを別々に分けたいと思います。

 ところで
 >ぼくも入れてください
 入ってきたのが運の尽き、一つだけ教えて下さい。

 アドレスを特定セルに出す場合、列を１つ右に　[27 を 28] にして
 その左 aa列に 1 から順番に数字を打ってその番号の行にアドレスを出したい。
 つまり aa3 に 3 なら ab3 にアドレス　　　= c.Address(False, False) & "です"　　　を
 この考え無理でしょうか？　アドレスログを残したいのです。試行錯誤しましたが解りませんでした。
 駄目見本はこれです。黄色マークが点灯でした。

 Cells(Application.Max(Range("aa1:aa")), 28) = c.Address(False, False) & "です"

 完成品では無くても、ヒントをお願いします（完成品のほうがいい？？）　　　（jun53）

 今思いつきました。
 １列右にコピーするボタンを作ってそれをクリックする。
 スマートじゃ無いし作業列作るけど、最悪これでもいいですかね。　　　（jun53）

 何考えてるんでしょ、結局一行目だから上書きされちゃう。これ取り消しです。

 Cells(i + 1, 28) で　Dim　宣言　？？
 ここ全然理解できてない。。　すみません。　　　（jun53）

 ちょっと休憩で見に来たんですけど、
 >ぼくも入れてください
 これはきっと通りすがりの人だと思います。
 また、（ken!）と名乗るのは、弥の付く人が良く使う手法です。
 きっと私ではありません（笑）。

 aa列に1から順番に～　というところがよくわかりません。
 1回目の検索結果をab1に、2回目の検索結果をab2にということなんでしょうか？

 きっと弥のつく人がなんとかしてくれると信じて...

 （ramrun）これより明日の昼まで休み無し（汗）。

 ramrunさん、すみません。「列を１つ右に」の件、全面取り消します。
 ここまででもいいものが作れそうです。ありがとうございました。
 ケンさん、スレ　ありがとうでした。　　　（jun53）

 なんや、やっとお呼びがかかったのに要らんのんかいな。それともワタイの登板を拒否
 するっちゅう魂胆でんな。（笑
 >「列を１つ右に」の件、全面取り消します
 当然でんがな。マクロをいじる人間がそないな野暮ったい作業するんは止めてや。
 作業は全てマクロに任す。自分は煙草をくゆらしながら鼻くそほじくる。
 これぞＶＢＡを扱う本分ちゅうもんですわな。
 　ほなまた迷い込んでくるんを祈って　（弥太郎）

 ちょっと仕事してる内にだいぶ話しが進んでいるみたいですが、もう1つ気になっている事があります。
ramaunさんの言っている（ken!)さんかなぁ～？
＞これ1件しか検索しないってことでいいんですか？
です。2つ、もしくは3つ同じ数字が有る時はどんな感じになるのでしょう？単純にExit　Subを消しても
出来ないし、ヒットしたアドレスをどこかに格納しておかなければ、いけないし???頭パンク状態です。
2人のken!さん教えてください。もちろん3,4人目のken!さん歓迎です。（ケン）

 junちゃんの表がドナイなっとんかわかりまへんねん。Max使うとるさかい条件の合うの
 はひとつッちゅう事になる筈ですわなぁ。ですから１つの場合はjunちゃんの言うたと
 おりでけますけど、さあ、ヒットするんが多数有った場合どこへ書き出したらよろしい
 んやろか？
 もう勝手にＡＢへ書き込みましたで。そこら辺は考えてどこか指定しておくんなはれ。
 　もう寝るべ（正直者のken!)
 '-------------
 Sub SearchNumber2()
    Dim adr As Object, c As Object
    Dim X As String
    Dim adrs()

    Set adr = Range("A1").CurrentRegion
    adr.Interior.ColorIndex = xlNone
    Cells(1, 28).ClearContents

    X = Application.InputBox("検索する数値は？", "", "半角数字で入力して下さい。")
    If X = False Then Exit Sub
    i = 0
    For Each c In adr
        If c = X Then
            ReDim Preserve adrs(i)     '↓これも範囲が分かれへん
            Cells(Application.Max(Range("aa1:aa65536")), 28) = c.Address(False, False) & "です"
            c.Interior.ColorIndex = 3
            adrs(i) = c.Address(False, False) & "です"
            i = i + 1
        End If

    Next c
    If i = 0 Then MsgBox "有りまへんでしたわ": Exit Sub

    n = i - 1
    For i = 0 To n
        Cells(i + 1, 28) = adrs(i)
    Next i
 End Sub

　弥太郎さん、度々有難う御座います。奥が深い・・・まだまだ、弥太郎さんの爪先にも、
達してませんね。精進します。（ケン）

 弥太郎さん、毎度どーもです。
 Max なんて知ったかぶりのコードを書いて混乱させました。質問の書き方って難しいです。
 考えは単純で、アドレスログを残したい　ということなんです。　恥ずかしいですが作業列を作って対応したところでした。。

 例を言えば　検索範囲は [a1:z10000]
 １回目の検索で Cells(1, 28) にアドレス、２回目で Cells(2, 28) に、３回目で Cells(3, 28) に、
 以下順々に下行へ、、、アドレスログは消さない。 Cells(1, 28).ClearContents   の部分は無くていいのです。
 今の考えではせいぜい 100 回 検索分[ 100 行　 Cells(100, 28) ] くらいまでログが残れば、と考えてます。
 わざわざ書いて頂いたのですが、今日はもうドライアイ状態で少し疲れました。あすゆっくり拝見致します。
 ともかくも有り難うございます。

 ケンさん
 重複番号の処理、、ramaunさんが確かに言ってましたが今回私の場合は
 一番最初にヒットしたアドが欲しかったので、つい流してしまいました。
 それに重複全部探すと10個、20個、あるいはそれ以上になり収拾がつかなくなるおそれもありましたし。
 弥太郎さんのコードで解消されてるといいですね。
 それでは、少し早めのお休みなさい。　　　（jun53）

 弥太郎さん、ありがとうございます。

 If を使って
 Offset
 RowOffset
 Range 
 Cells            等々
 色々組み合わせたけど、、、

 １回目検索が済んで Cells(1, 28) に　例えば　Z5000　が入ってると　つまり Cells(1, 28) <> ""  なら
 ２回目の検索では Cells(2, 28) に表示するんですよー
 と認識させればいいんですよね。　しかし、、解りません。。
 此処の過去ログで似たようなコードを見たような気もするけど、、

 この段階で中断してると先に進めませんので、
 ここはひとまず、これで [ 済み ] という事にさせて下さい。勝手ですがすみませんです。
 皆様色々有り難うございました。　　　（jun53）

コメント返信：

[ 一覧(最新更新順) ]

[[20031027191229]] 『VBA：最終列の取得』（ケン） ページの最後に飛ぶ

[[20031027191229]]　『VBA：最終列の取得』（ケン）　ページの最後に飛ぶ