[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『指定範囲の最大最終行』(T17)
お世話になります。
指定した列範囲の「最大最終行まで」を選択するにはどうしたらいいでしょうか?
・範囲内には空白セルがあります
・罫線(表)がある場合もあります
例で「A,B,C列」を指定した場合、求める範囲は「A1:C5」というものです。
(R/C) A B C D … 1 20 30 40 … 2 10 31 41 … 3 11 22 32 … 4 23 … 5 24 44 … 6 45 …
ループで逐一セルを確認すれば何とかなりそうですが、できればSpecialCellsやUsedRange等を
うまく組合せてできないものかと思っています。
< 使用 Excel:Excel2010、使用 OS:Windows7 >
ご参考に
(++) 2017/10/04(水) 13:59
変化球
Sub tst() Dim S As Integer Dim L As Integer
S = [MATCH(0,INDEX(0/((A1:A1000<>"")+(B1:B1000<>"")+(C1:C1000<>"")),0),0)] L = [MATCH(1,INDEX(0/((A1:A1000<>"")+(B1:B1000<>"")+(C1:C1000<>"")),0),1)] Range(Cells(S, "A"), Cells(L, "C")).Activate End Sub
(ねむねむ) 2017/10/04(水) 14:17
上記は1000行目までを対象としている。 列指定でもできるがその場合は少し時間がかかる。 (ねむねむ) 2017/10/04(水) 14:18
ループで総当たりまでしなくても、指定範囲の各列の最終行を取得して、 最大値が範囲の最終行ですよね。
ループはループですけど例で言ったらend(xlup)を各列で計3回実行するだけですよね。 それでは駄目なんですか?
(sy) 2017/10/04(水) 14:43
++様
ご紹介のサイトを見ても解決できずに今回の投稿となりました。
最後の方の<S6>が示唆に富んでいるように思ったのですが、私のレベルでは...(涙)
ねむねむ様
すごい発想ですねぇ〜・・勉強になります。
列数を増やすには「+」していけばいいんですね。
列数が多くなると・・ご回答に手を加えてやはりループですかねぇ..
sy様
ループが絶対ダメということではなく、学習の一環としてループ意外の方法があるのかないのか..
という観点で投稿しました。
(T17) 2017/10/04(水) 15:07
'指定列を選択した状態で実行 Dim r As Range, c As Range Set r = Selection Sheets.Add before:=ActiveSheet r.Copy ActiveSheet.Range("A1") Set c = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell) Set c = r.Cells(1).Resize(c.Row, c.Column) Application.DisplayAlerts = False ActiveSheet.Delete c.Select End Sub
(mm) 2017/10/04(水) 15:49
なるほど、最初に指定範囲を切り取っちゃうんですね。
最初は何をしているのかチンプンカンプンでしたが、
「c」の中身を確認したりしてようやくわかりました。
いろいろ応用が利きそうで勉強になりました。
(T17) 2017/10/04(水) 16:57
前にも言ったと思うけど、
自分でループを書かないからと言って、裏で誰かが作ってくれたプログラムがループしてるんだろうから、「ループしないで」とか「一行で」とかほとんど意味ないと思います。
まぁ、VBAでループ処理を書くよりエクセル君に任せた方が大抵は速いと思いますが、
一概にどっちが速いかはやってみないとわからないと思います。
動作確認してないけど、検索機能で行優先で逆回しで検索したら「最後の行」が見つかりそうなきがする。。。
Sub test()
Dim Rng As Range
Set Rng = Range("A:C")
MsgBox GetLastRow(Rng) & "行目" End Sub
Function GetLastRow(ByVal wRng As Range) As Long
With wRng GetLastRow = .Find(What:="*", _ After:=.Cells(1), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious, _ MatchCase:=False, _ MatchByte:=False, _ SearchFormat:=False).Row End With End Function (まっつわん) 2017/10/04(水) 18:19
おそらく今回の質問なら以下の1行で質問の主旨は満足すると思います。
質問が質問なので他にもトリッキーな回答が多いですけど、 そう言う雑学的な知識を幾ら知ってても、 実践で簡単に効率良いコードを書けなければ意味ないと思いますよ。 回答者の皆さんはそれが出来るから、こう言うお遊びも色々アイデアが出てきますが。
それよりも同じ勉強するならループなどの基本をとことんまで追求する方が実践で有効と思います。
Sub test()
MsgBox Selection.EntireColumn.Find("*", , , , , xlPrevious).Row
End Sub
(sy) 2017/10/04(水) 21:05
SearchOrder忘れてた.
すいません。 こうでした。
Sub test()
MsgBox Selection.EntireColumn.Find("*", , , , xlByRows, xlPrevious).Row
End Sub
(sy) 2017/10/04(水) 21:08
回りくどいことすると Sub a() With Columns("A:C").SpecialCells(xlCellTypeConstants) Debug.Print .Address Debug.Print Range("A1", Split(.Address, ",")(UBound(Split(.Address, ",")))).Address Debug.Print Range("A1", Split(.Address, ",")(UBound(Split(.Address, ",")))).Rows.Count End With End Sub これでもいける? 一番最後の行は、Addressの最後になるらしいので、Splitで区切って一番最後のアドレスを抜き出して、 A1からの矩形範囲に変更し、ROWSを数える・・・ (稲葉) 2017/10/05(木) 08:44
いい方法が見つかれば、関数の中身を差し替えるだけですね^^
Function GetLastRow(ByVal wRng As Range) As Long
With wRng.SpecialCells(xlCellTypeConstants) With .Areas(.Areas.Count) GetLastRow = .Cells(.Cells.Count).Row End With End With End Function
Function GetLastRow2(ByVal wRng As Range) As Long
Dim v v = Split(wRng.SpecialCells(xlCellTypeConstants).Address, "$") GetLastRow2 = v(UBound(v)) End Function
SpecialCellsは飛び飛びのセル範囲の数に制限があると思うので、
少し気を付けないといけないかもです。
(まっつわん) 2017/10/05(木) 11:24
>Split(wRng.SpecialCells(xlCellTypeConstants).Address, "$") そうですよねぇ、そのほうが早いですよねぇ・・・ (稲葉) 2017/10/05(木) 11:39
私のレベルでは何がトリッキーで何がオーソドックスかは判断つきませんが、
まっつわん様やsy様のいわんとされていることは理解しているつもりです。
今回はループ処理しようとしていたところ、++様も紹介されていたサイトをみて、
エクセルの標準機能?で何とかなるんじゃないか..と思ったのがキッカケでしたが、
いろいろな方法があることに“刺激”を受けました。
(回答者のみなさんのアタマの中をのぞきたくなりました)
今は、FindやSplitなどふだん馴染みのない関数を理解するのに精一杯です。
特に稲葉様のDebug.Printの2行目とかまっつわん様のSplit(...,"$")のところは、
ほぼ“お手上げ”状態です..(涙)
(T17) 2017/10/05(木) 15:30
本来は件のサイトに尋ねるべきかとは思いますが、よろしければ解説いただきたく...
(T17) 2017/10/05(木) 16:15
オブジェクトの代入にはSetを使う。 Setを使わない代入は値の代入になるため右側にオブジェクトを指定していてもそのオブジェクトの値が代入される。 なのでSetを使った代入を行えばFindメソッドで見つかったRangeオブジェクトそのものを代入できる。 かなり大ざっぱな説明だが。 (ねむねむ) 2017/10/05(木) 16:28
>「24」が返ってきます。 >これって「セル内のデータ」ではないのでしょうか? 「既定のプロパティ」で検索してみてください。 今回の場合は、「.Valueが省略されている」と解釈されたようです。
Sub test() Dim v
Let v = Selection.EntireColumn.Find("*", , , , xlByRows, xlPrevious) MsgBox TypeName(v)
Set v = Selection.EntireColumn.Find("*", , , , xlByRows, xlPrevious) MsgBox TypeName(v) End Sub
(まっつわん) 2017/10/05(木) 17:10
>今は、FindやSplitなどふだん馴染みのない関数を理解するのに精一杯です。 Findは関数じゃなくメソッド(=命令)です。(返り値のあるメソッド) そして、これは標準機能の検索のことです。 検索の文字を「*」とすることで、「表示されている何かの文字」という意味になるので、 行優先で逆順(xlPrevious)で検索したらよいだろうということです。
Splitは文字列を特定の区切り文字で分割して一次配列で返す関数です。
稲葉さんが
>>一番最後の行は、Addressの最後になるらしいので
こういう情報をくれたので、あとは試してみたらいいだけですよね?
適当にセルを飛び飛びで選択して、イミィデイエイトウィンドウで、
?selection.Address[Enter]
(※[Enter]はエンターキー押下の意)
としてみれば、
$B$7,$A$19,$B$23,$D$22,$D$15,$C$17
こんな文字列が返ってきます。これを何例かためしてみると、
そうしたら、「あぁ、そうか。最後の17をどうにかして取り出せばいいのか」と、
解ると思います。
あとは、文字列を扱う関数でどれが利用できるか探せばいいわけです。
Sub test()
Dim v
v = "$B$7,$A$19,$B$23,$D$22,$D$15,$C$17"
MsgBox Mid(v, InStrRev(v, "$") + 1) End Sub
こんなのでも行けますね。
>まっつわん様のSplit(...,"$")のところは、ほぼ“お手上げ”状態です..(涙)
様はこっぱずかしいのでやめてください^^;
最後の17を取り出すのに、
Splitを使って「$」で区切れば、配列の一番後ろに欲しい値がくるので、
あとは配列の一番後ろをどうやってもとめるか?
配列のUBoundで求められる番号の値ですよね?
ステップ実行しながら、「ローカルウィンドウ」で、変数の中身を確認しながら確認してください。
配列の場合は「+」のところクリックで中身が展開されます。
(まっつわん) 2017/10/05(木) 17:13
ねむねむ様
ローカルウィンドで「set」で代入したのが「Object」になっているのは確認できました。
表示は同じ「24」なんですね。
「.Row」を付けると「6」と行が表示されるので、てっきり「$B$6」とでも表示されるものと
思っていました。
まぁオブジェクトが何たるかがわかってないということですね。
本題から脱線してすみませんでした...
まっつわん様
「$」はセル表示の「$」そのものだったんですかっ。
てっきり「*」の類(正式な言い方知りません)と一緒だと思っていました。
どうしようもないですねぇ〜
あとは「Range("A1", Split(.Address, ",")(UBound(Split(.Address, ","))))」ですか。
「)」の数合ってんのとか、いきなり「(UBound(Split(...」をくっつけられるの?とか
そもそも何をされてるのか...
(T17) 2017/10/05(木) 17:46
後は分解するだけなんだから、自分で出来そうなものですが・・・ Sub a() Dim v As Variant With Columns("A:C").SpecialCells(xlCellTypeConstants) Debug.Print .Address(0, 0) v = Split(.Address(0, 0), ",") Stop Debug.Print Join(v, "-") Debug.Print "v(UBound(v)) = v(" & UBound(v) & ") = " & v(UBound(v)) End With End Sub (稲葉) 2017/10/05(木) 19:11
>後は分解するだけなんだから、自分で出来そうなものですが・・・
そうですね、少々みなさんのご好意に甘え過ぎたようです..すみません。
後のコードは見ずにもう一度考えてみます。
(T17) 2017/10/05(木) 23:00
ステップインで1行づつ実行しながら、
ローカルウィンドウで変数の中身を確認しましたか?
変数名の頭の「+」のところをクリックしたら、配列の中身も確認できます。
(2度目の言及です。)
>いきなり「(UBound(Split(...」をくっつけられるの?
できます。
左から読んでいって、返り値に対してまた何かをするのは同じです。
Wrokbooks(1).sheets(1).Range("A1").select
~~~ヘルプ(2003)より~~~
Sheets プロパティ
Workbook オブジェクトでは、指定されたブックのすべてのシートを表す Sheets コレクションを返します。値の取得のみ可能です。
~~~引用終わり~~~
↑ここで、値とはオブジェクトのことと理解してください。
ということは、上記のコードは、
ワークブックスプロパティが返すワークブックスオブジェクトの、
シーツプロパティが返す、シートオブジェクトの、
レンジプロパティが返すレンジオブジェクトを選択する。
ということをしたい意思表示です。
配列を返す関数でも同じです。
(しかし、「同じことを2度書かない」という原則から外れますので、
変数を使ったほうがよりよいです。ま、メンテナンスを考慮しないならどっちでもいいです。)
つまり、
Sub test()
Dim wb As Workbook Dim sh As Worksheet Dim Rng As Range
Set wb = Workbooks(1) Set wh = wb.Sheets(1) Set Rng = sh.Range("A1")
Rng.Select End Sub
と、同意です。
ステップ実行をしながローカルウィンドウで変数の中身を逐一確認してみてください。
「オブジェクトとは何ぞや?」の一部が覗けるはずです。
>ローカルウィンドで「set」で代入したのが「Object」になっているのは確認できました。
これではちゃんと確認できてないということです。
「+」を押して中身を展開してみてください。
>表示は同じ「24」なんですね。
↑これも考察不足です。
「既定のプロパティ」を検索してみましたか?
話がとっ散らかって、どこに焦点を当てて話をしていいかよくわかりませんね^^;
あと、愚痴ですが、
ここのサイト色とか使って強調とかできないんですね^^;
それが読みやすいかどうかは個人によって違うとは思いますが。。。
個人的には読みにくいので、覗く頻度が他のサイトとより少なくなります。
でも、一番ここがにぎわってるんですかね?
一般の質問もマクロの質問もごちゃまぜだからそう感じるのだろうか?
ま、マクロ本気で勉強してるものにとっては、
マクロだけの話に分けてもらったほうが良いような気がしないでもないですが。。。
本気で勉強してるなら、いろいろ回って情報収集をするのもありだろうとは思います。
(まっつわん) 2017/10/07(土) 09:08
稲葉さん SpecialCellsを使用するときには、データが無い時のエラー処理をしておかないと... (seiya) 2017/10/07(土) 13:49
いろいろ弄って確認しましたが、Rangeの書き方で躓いており教えていただけますか?
稲葉様の最初のコードにある、
「Range( "A1", Split(.Address, ",")( UBound( Split(.Address, ",") ) ) ).Address」
は「Range("A1","B3:B5").Address」と同意と考えてよろしいのでしょうか?
「Split(...)(UBound(Split(...)))」の部分は「"$B$3:$B$5"」となるのでそう考えましたが、
そうなるとRangeでセル参照を2つ指定する書き方は、Range("A1","B5")のように“単一セル”
でなくてもOK?..とここで思考停止しております。
ちょっと脱線気味ですが、よろしくお願いいたします。
(T17) 2017/10/11(水) 17:37
いただいたみなさんのご回答を肥しにして、ステップアップできればと思います。
これからもよろしくお願いいたします。
(T17) 2017/10/12(木) 14:15
配列の中身を確認する
http://www.relief.jp/docs/018249.html
プロパティ、メソッドの探り方 マクロ記録とF1のHelpを使う
http://www.ken3.org/vba/excel-help.html
既定のプロパティ(標準のプロパティ?)の話
https://msdn.microsoft.com/ja-jp/library/az06zx4y(v=vs.90).aspx
↑から抜粋
既定のプロパティ
Visual Studio 2008
更新 : 2007 年 11 月
引数を受け取るプロパティは、クラスの既定のプロパティとして宣言できます。既定のプロパティとは、オブジェクトに対して特定のプロパティが指定されていない場合に Visual Basic が使用するプロパティです。既定のプロパティを使うと、頻繁に使用するプロパティ名を省略してソース コードをコンパクトにできます。
既定のプロパティにするのに最も適しているのは、パラメータを受け取るプロパティのうちで最もよく使用するプロパティです。たとえば、コレクション クラスの Item プロパティは頻繁に使用されるので、既定のプロパティにするのに適しています。
「宣言できます」というのは開発者向けの話ですが、
既存のクラス(≒オブジェクト)、例えばRangeなどにも当然宣言してあります。
http://officetanaka.net/excel/vba/beginner/08.htm
http://www.relief.jp/docs/excel-vba-range-object-default-member.html
「他人を上手に使える俺すげぇ」ってやってるだけでなく、
自分でちゃんと必要な情報が探せるようになりましょう><
(まっつわん) 2017/10/12(木) 18:00
>無視されてるのか、パニクってるのか解らないけど・・ >「他人を上手に使える俺すげぇ」ってやってる・・
いただいたコメントに対しては私なりに理解に努めており決してご指摘のようなことはありません。
(完全に咀嚼できたか?..と問われると困りますが.. _ _;)
そのように誤解されたとすれば、ひとえに返信のしかたが悪かったのですね、以後気をつけたいと思います。
配列や既定のプロパティ云々のところは、ご紹介サイト含めいろいろ調べましたがまだまだ理解不足と
認識しておりますので、別途投稿させていただくかも知れません。
10/11付けのRangeの件は“脱線気味”とお断りしたように、配列云々以前の初歩的な箇所での話であり
(ある意味恥しいです)、上述の“別途投稿..”の想いと併せて本件についてはこれ以上は
長くできないかと思って“自己解決”としました。
>自分でちゃんと必要な情報が探せるようになりましょう><
はい、 エクセルに限らず検索のしかたって(ツール含めて)大事ですよね。
単に時間の問題だけでなく、“できる・できない”の差って案外ここにあると日々痛感しております。
これからもよろしくお願いいたします。
(T17) 2017/10/13(金) 10:04
…と書こうとしただけだったのですが、皆さんの回答とは別案を思いついたので、書いておきますね。
あまり検証してはいないのですが、SpecialCellsで複数範囲を得た場合、必ず行の大きいエリアが最後になるように見えたので、それを利用した案です。試してみてください。
Sub test() Dim cw As String
cw = Range("A:C").SpecialCells(xlCellTypeConstants).Address cw = Mid(cw, InStrRev(cw, "$") + 1)
MsgBox Range("A1:C" & cw).Address(0, 0) End Sub (???) 2017/10/13(金) 10:52
>できる、できないの差って、できるまで続けるか、途中で諦めるかの違い >同様に、理解不足ではなく、理解するまで調べるのを途中で諦めた・・
自分に厳しい人でないとここまでは書けないですよね、???様らしいコメントだと感服します。
(本校での自他への???様のコメントからの素直な感想です)
で、「InStrRev」ですが、全く知らない関数でした。
これでまた一つ「引出し」が増えて嬉しく思います。
実は先の稲葉様とまっつあん様の「$」の話の時にも思ったのですが、ご提示のコードで
「cw=Right(cw,1)」とするのは、たまたま結果オーライなだけでしょうね?
・・・自分で調べるべき..ですかね、やはり <_ _>
(T17) 2017/10/13(金) 11:53
そこで、後ろから$を文字列検索して、数字部分が何桁でもOKにした訳です。(Mid関数の第3引数を省略しているのは、こういう書き方をすると、VBAは末尾まで全てを取り出してくれるからです)
InStrRevは、たしかVB6で実装された命令であり、大元のBASIC言語には無かったので、知らなくても問題ないです。 VBAは、しっかり最後のVBの命令も網羅してくれているのですね。 とはいえ、今回のように特定文字列以降を切り出したい時とか、フォルダのフルパスから末尾のファイル名のみや、拡張子のみを取り出すときに便利なので、覚えておいて損はないでしょう。
(???) 2017/10/13(金) 14:21
「Mid関数の第3引数を省略」・・・エクセル関数式ではエラーになってもVBAではOKなんですね。
これでまた1個「引出し」が増えました、 ありがとうございます。
(T17) 2017/10/13(金) 15:30
>「Mid関数の第3引数を省略」・・・エクセル関数式ではエラーになってもVBAではOKなんですね。
良いところに気がつきましたね! その通り、マクロだけの特徴です。(過去のBASICでも、省略は不可でした)
シートの数式で後ろ全部取り出したい場合は、99とか、実際の文字長より大きい値を指定することで、同様に末尾まで全てを得る事ができますので、併せて覚えておくと良いでしょう。
(???) 2017/10/13(金) 16:21
>たまたま結果オーライなだけでしょうね? >・・・自分で調べるべき..ですかね、やはり <_ _>
本来、自分で考察探求すべきですが、
経験がないとテストパターンにどうしても抜けが生じるのはやむを得ない。
なので、ここで愚痴りながら話を続けるのはありです。
しかし、
回答者にはあなたがどんなことを今やってみたのか、
そしてどこまで理解できたのか説明をしなければ、
回答者にはわかりません。
なので、自分なりの言葉で、自分なりの結論を一応書いてみて、
それに対して批評を乞うのがいいかなと僕は思います。
>全く知らない関数でした。
>これでまた一つ「引出し」が増えて嬉しく思います。
少ない経験を補うには掲示板は課題の宝庫です。
いろいろな掲示板を巡回して課題をみつけ取り組んでみてはいかがでしょうか?
(回答する気で書いてみて、他の方の回答と見比べて勉強するのは、
結構いい勉強方法だと思いますけど?)
そうすれば、色々な関数や知らないことの遭遇する回数も増えますよ^^
で、「オブジェクト」は理解できたのだろうか。。。。(ま、いっか。。。)
(まっつわん) 2017/10/13(金) 16:55
Dim s
s = "$A$1,$B$2:$B$10" s = StrReverse(s) s = Val("0." & s) s = StrReverse(s) MsgBox CLng(s) End Sub
文字列をひっくり返してVal関数で数値にする方法もないことはないですね。
とにかくたくさん、人が書いたコードを見ないと、
本とにらめっこしてても、なかなかいろいろな方法論を見つけるのは難しいと思います。
(まっつわん) 2017/10/13(金) 17:06
まっつわん様
いろいろとアイデアが湧いてくるんですねェ〜、本当にアタマを割って
中をのぞいてみたく思います。
お持ちの「引出し」の容量が大きく数が多いのと、やはり「情熱」でしょうかね。
>で、「オブジェクト」は理解できたのだろうか・・・
いえいえ、オブジェクト、プロパティ、メソッド..わからないことだらけです。
「Rangeは、オブジェクトでありプロパティでもある・・」みたいな説明を見た時には、
???を通り越して腹がたちましたからね。(笑)
エクセルを使って何かしようとして、「わからない」には2つあると思っています。
・やりかたが、わからない(方法や理論?)
・ツールが、わからない(関数や決まった手順など?)
どちらも無数にあるように思えるし、相互に絡み合っていますからね。
(理論がわかってもツールがないことには...逆もしかり)
一度は系統だった学習を..と思わないではないですが、実践では本校への自他投稿で間に合ってしまい、
なかなか怠惰な性格は直せないですね... ^ ^;
本校を知った最初の頃は他の人への本校の回答をそのまま拝借している場合も多く、それらを自分なりに
咀嚼して改良?していくのも学習には有効かなと思っています。
これからもよろしくお願いいたします。
(T17) 2017/10/15(日) 13:27
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.