- ベストアンサー
VBAで最終行／処理対象のデータまでを取得する方法
real beatin（@realbeatin）の回答
（No.3の続き） さて、主題であるCurrentRegionの扱いについてですが、 Excelの用語としては、「アクティブ セル領域」と表現されることが多いですが、 VBAでは、range.CurrentRegion 「指定のセルを含み、空白の行と列で囲まれているデータ領域（連続領域）」 といった表現になるのでしょうか。丸めると、 「指定のセルを含む一連なりの表全体の範囲」という言い方も出来そうです。 ひとつのシートに複数の表があるかもしれないけれど 一応、ひとつのテーブルと看做して問題ない という、前提条件、でこそ意味を持つのが、CurrentRegionです。 A B C 1 [id] [Name] [Mail] 2 id1 uName1 Url1 3 id2 uName2 Url2 4 id3 uName3 Url3 のような表について、VBAで、この表全体を取得するには、 Cells.CurrentRegion Cells(1).CurrentRegion Range("A1").CurrentRegion Range("B2").CurrentRegion Cells(4, 3).CurrentRegion 表の中にあるセルであれば、どのセルを指定しても、 「一連なりの表全体の範囲」として、 Range("A1:C4")を取得できます。 > Range("A1").CurrentRegion.Cells(Range("A1").CurrentRegion.Cells.Count).Row Cellsは省略可能で Range("A1").CurrentRegion(Range("A1").CurrentRegion.Count).Row と書くことも出来ますが、 先に挙げたシートサンプルで言えば、 Range("A1:C4")(Range("A1:C4").Count).Row と、この場合同じ意味になります。 Range("A1:C4").Countは4*3で12ですので、 Range("A1:C4")(12).Row に相当します。 [A1:C4]というセル範囲の、'12'番めのセルの、'行インデックス' と翻訳したとして、疑問に残りそうなポイントは、 「'12'番め」でしょうか。 指定した範囲[A1:C4]の 先頭（左上）セルからまず右方向に数え始めて、 終端列まで数えたら、次の行の一番左のセルを数える という順番になります。 A1-Range("A1:C4")(1) B1-...(2) C1-...(3) A2-...(4) B2-...(5) C2-...(6) A3-...(7) B3-...(8) C3-...(9) A4-...(10) B4-...(11) C4-Range("A1:C4")(12) Range("A1:C4")(12)は[C4]です。 Range("A1:C4")(Range("A1:C4").Count)は領域の最後のセルを指し[C4]です。 Range("A1:C4")(Range("A1:C4").Count).Row は、領域の最後のセルの行インデックス=4です。 Range("A1").CurrentRegion(Range("A1").CurrentRegion.Count).Row と書いても、この例では、同じ意味になるので、 連続領域の最後のセルの行インデックス=4です。 総じて、求めようとしているのは Range("C4").Row [C4]の行インデックスとしての、4、です。 > Sheets("テスト").Range("A1:C" & Sheets("テスト").Cells(1).CurrentRegion.Rows.Count) 先に挙げたシートサンプルに照らして、 シートの指定を省略すれば、 Range("A1:C" & Cells(1).CurrentRegion.Rows.Count) となります。 Cells(1).CurrentRegion は[A1:C4]なので、 Range("A1:C" & Range("A1:C4").Rows.Count) と同じ意味です。 Range("A1:C4").Rows は、[A1:C1],[A2:C2],[A3:C3],[A4:C4]という ４つのセル範囲オブジェクトをそれぞれの行として捉えたコレクションです。 Range("A1:C4").Rows.Count で、Range("A1:C4")の行数を求めると、=4です。 ここで求まった'4'を Range("A1:C" & Range("A1:C4").Rows.Count) の括弧の内側に代入すると、 Range("A1:C" & 4) つまり Range("A1:C4") ということになります。遡って、 Range("A1:C" & Cells(1).CurrentRegion.Rows.Count) Sheets("テスト").Range("A1:C" & Sheets("テスト").Cells(1).CurrentRegion.Rows.Count) は、この例では、 Range("A1:C4") を指します。 総じて、求めようとしているのは セル範囲[A1:C4]です。 また、 > Range("A1").CurrentRegion.Cells(Range("A1").CurrentRegion.Cells.Count).Row は、 With Range("A1").CurrentRegion returnV = .Cells(.Cells.Count).Row End With のようにも書けるのですが、 場合によっては、 With Range("A1").CurrentRegion returnV = .Row + .Rows.Count -1 End With のように、連続領域の 先頭行のインデックス、と、行数、を加えて、１引く というやり方で求められます。 先頭行のインデックス、行数、最下行のインデックス、 ３つの値を求めたい場合などでは、後者のやり方になります。 With Range("A1").CurrentRegion firstRow = .Row cntRows = .Rows.Count lastRow = firstRow + cntRows -1 End With 前者のやり方では、最下行のインデックス、しか求められませんから、 【目的】によって書き分ける、ということになります。 ただ、そもそもの、CurrentRegionを使う理由、というのは、 処理したいセル範囲を固定的絶対的に捉えることのできない、 可変で曖昧さの残るような【条件】だから、という点も 忘れないでください。 また、Range("A1").CurrentRegion、Cells(1).CurrentRegion どちらも、[1:2]行が空、[A:B]列が空、いずれかの【条件】下 では、[A1]を参照するだけで、表全体を捉えることにはならない、 ということも知っておいて下さい。 加えて、[シートの保護]が適用されたシートでは、 CurrentRegionはエラーになるので使えません。 このことも書き分ける【条件】のひとつとして 憶えておいた方がいいでしょう。 ついでに、 range.SpecialCells(xlLastCell) について。 当該シートで、 Worksheet_SelectionChange イベント を使う場合には、ご注意を。 range.SpecialCells(xlLastCell) を一度実行するだけで、 Worksheet_SelectionChange が２回、コールされます。 Worksheet_SelectionChange プロシージャの書き方次第では、 トラブルの元になるということも、 一応知っておいてください。 これこそ、確認を怠って他人に薦めてはいけない、 という風に私は思っています。 まぁ回答者としては、 質疑が重くなるし、本題とそれたことを書くのも気が退けるので、 他に方法が無い場合にだけ注釈付で使うのが、 SpecialCells(xlLastCell)だったりします。 自分で書く分には、すべてを掌握している訳ですから、 気を抜いていても問題になることは少ないですけれど、 このことを知らないでいると、 後になってWorksheet_SelectionChangeを追加する時に、 原因不明のトラブルに戸惑うこともあるようです。 実践の中で迷いを減らす意味で整理しますが、 処理対象のセル範囲 そのもの(Range)を取得 の先頭行・先頭列のインデックス(数値) のセル範囲の行数・列数(数値)を取得 の最下行・最終列のインデックス(数値)を取得 のセル数(数値)を取得 など、様々な処理の中で、どれを選択（組合わせ）するのが、 今やりたい処理に最適なのか、とか、 それぞれの処理で求めようとしているのは何なのか、 意識して常に把握できるよう心掛けてください。 基本的で、当たり前のことのようですが、 結構迷子になっている方、見掛けます。 【目的】や【条件】に合わせて書き分けられるように なるまでは時間掛かると思いますが、急がなくて大丈夫です。
関連するQ&A
- EXCEL VBA 早く処理をする
よろしくお願いします 下の構文を標準モジュールに書き込み、callで実行しているのですが 処理に時間がかかります。 処理を早くする方法と構文の簡素化のご教示をお願いします。 Application.ScreenUpdating = False For i = 1 To 12 With Worksheets(i) .Select LastRow = .Range("A150").End(xlUp).Row + 1 .Range("A8:G" & LastRow).Sort Key1:=Range("A8"), order1:=xlAscending .Range("G8:G" & LastRow - 1).Formula = "=G7+E8-F8" LastRow = .Range("A150").End(xlUp).Row + 1 .Range("A" & LastRow).Select Dim EndRow As Long EndRow = .Range("A" & Rows.Count).End(xlUp).Row Cells(Rows.Count, 1).End(xlUp).Offset(1, 3) = .Name & "合計" Cells(Rows.Count, 1).End(xlUp).Offset(1, 4) = Application.WorksheetFunction.Sum(Range("E7:E" & EndRow)) Cells(Rows.Count, 1).End(xlUp).Offset(1, 5) = Application.WorksheetFunction.Sum(Range("F7:F" & EndRow)) Cells(Rows.Count, 1).End(xlUp).Offset(2, 3) = "前月繰越" Cells(Rows.Count, 1).End(xlUp).Offset(2, 4) = .Range("G7") Cells(Rows.Count, 1).End(xlUp).Offset(2, 5) = "" Cells(Rows.Count, 1).End(xlUp).Offset(3, 4) = "" Cells(Rows.Count, 1).End(xlUp).Offset(3, 3) = "次月繰越" Cells(Rows.Count, 1).End(xlUp).Offset(4, 3) = "合計" Cells(Rows.Count, 1).End(xlUp).Offset(3, 5) = Cells(Rows.Count, 1).End(xlUp).Offset(2, 4) + Cells(Rows.Count, 1).End(xlUp).Offset(1, 4) - Cells(Rows.Count, 1).End(xlUp).Offset(1, 5) Cells(Rows.Count, 1).End(xlUp).Offset(4, 4) = Cells(Rows.Count, 1).End(xlUp).Offset(2, 4) + Cells(Rows.Count, 1).End(xlUp).Offset(1, 4) Cells(Rows.Count, 1).End(xlUp).Offset(4, 5) = Cells(Rows.Count, 1).End(xlUp).Offset(3, 5) + Cells(Rows.Count, 1).End(xlUp).Offset(1, 5) Cells(Rows.Count, 1).End(xlUp).Offset(1, 6) = "" Cells(Rows.Count, 1).End(xlUp).Offset(2, 6) = "" Cells(Rows.Count, 1).End(xlUp).Offset(3, 6) = "" Cells(Rows.Count, 1).End(xlUp).Offset(4, 6) = Cells(Rows.Count, 1).End(xlUp).Offset(0, 6) .Range("C7").End(xlDown).Select Selection.Offset(4, 2).Borders(xlEdgeTop).Weight = xlHairline Selection.Offset(4, 2).Borders(xlEdgeBottom).LineStyle = xlDouble Selection.Offset(4, 3).Borders(xlEdgeTop).Weight = xlHairline Selection.Offset(4, 3).Borders(xlEdgeBottom).LineStyle = xlDouble Selection.Offset(4, 4).Borders(xlEdgeTop).Weight = xlHairline Selection.Offset(4, 4).Borders(xlEdgeBottom).LineStyle = xlDouble Selection.Offset(0, 2).Borders(xlEdgeTop).Weight = xlHairline Selection.Offset(0, 2).Borders(xlEdgeBottom).LineStyle = xlContinuous Selection.Offset(0, 2).Borders(xlEdgeBottom).Weight = xlThin Selection.Offset(0, 3).Borders(xlEdgeTop).Weight = xlHairline Selection.Offset(0, 3).Borders(xlEdgeBottom).LineStyle = xlContinuous Selection.Offset(0, 3).Borders(xlEdgeBottom).Weight = xlThin Selection.Offset(0, 4).Borders(xlEdgeTop).Weight = xlHairline Selection.Offset(0, 4).Borders(xlEdgeBottom).LineStyle = xlContinuous Selection.Offset(0, 4).Borders(xlEdgeBottom).Weight = xlThin End With Next i Application.ScreenUpdating = True
- ベストアンサー
- Visual Basic
- VBA 最終行を選んだシートにコピーする。
VBAど初心者です。どうしても最終行のデータを選んだシートにコピーできません。 LastRow.Selectのところで、止まってしまいます。どのように行を設定していいのかさっぱりわかりません。どなたか、ご指導のほどよろしくお願いします。 Sub copy_last_line() Dim LastRow As Long Sheets("Sheet1").Select LastRow = Cells(Rows.Count, 1).End(xlUp).Row LastRow.Select Selection.Copy Sheets("Sheet2").Select Range("A1").Select ActiveSheet.Paste Sheets("Sheet1").Select Range("A1").Select End Sub
- ベストアンサー
- Visual Basic
- ＜excel：VBA＞変数を使って簡略化したい
google検索してなんとか自力で作ったVBAを下記に貼りました。 きちんと動作はするのですが、せっかくなので変数を使って簡素化し、 データが多くても動作が速くなるようにしたいのです。 いろいろ試しましたが、変数の使い方の知識が乏しく、うまくいきませんでした。 変数としたいのは■マークの2箇所になると思います。 詳しい方、力を貸していただけないでしょうか。 どうぞよろしくお願いいたします。 ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■ Sub オートフィルタ貼付作業() With Sheets("データ").Range("A3") Application.ScreenUpdating = False Range("AA3:EK3").AutoFilter .AutoFilter Field:=1, Criteria1:="1" ’■Fieldが1ずつ増えていく Range("AA3").Copy Range("Z3") ’■AA3が1列ずつ右へずれていく .CurrentRegion.Copy Sheets("貼付").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Range("A3").AutoFilter Range("AA3:EK3").AutoFilter .AutoFilter Field:=2, Criteria1:="1" Range("AB3").Copy Range("Z3") .CurrentRegion.Copy Sheets("貼付").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Range("A3").AutoFilter ～～～～～～～～～～～～ 115列分のデータがあり 下記まで同じようにつづきます ～～～～～～～～～～～～ Range("AA3:EK3").AutoFilter .AutoFilter Field:=115, Criteria1:="1" Range("ek3").Copy Range("Z3") .CurrentRegion.Copy Sheets("貼付").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) Range("A3").AutoFilter End With Application.ScreenUpdating = True Sheets("貼付").Activate Cells.Columns.AutoFit End Sub ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
- ベストアンサー
- Visual Basic
- エクセルVBA 最終行にデータを追加する
エクセルのユーザーフォームにチェックリストを用意しました CheckBox1～CheckBox5まであり、 CheckBox1をクリック（true）にすると、セルに“あ” CheckBox2をクリック（true）にすると、セルに“い” CheckBox3をクリック（true）にすると、セルに“う” CheckBox4をクリック（true）にすると、セルに“え” CheckBox5をクリック（true）にすると、セルに“お” を反映させようと思っています たとえば、 ・CheckBox1のみクリック（ｔｒｕｅ）で、Ａ１に“あ” ・CheckBox2のみクリック（ｔｒｕｅ）で、Ａ１に“い” ・CheckBox1、CheckBox3をクリック（ｔｒｕｅ）で、Ａ１に“あ”、Ａ２に“う” ・CheckBox２～CheckBox5をクリック（ｔｒｕｅ）で、Ａ１に“い“、Ａ２に“う“、Ａ３に”え”、Ａ４に“お” といった感じで、選んだチェック項目について、Ａ列においてＡ１から次々とデータを入力しようとしています そこで、 sheets1.Range("Ａ:Ａ").Clear If CheckBox1.Value = True Then sheets1.Cells(Rows.Count, 1).End(xlUp) = "あ" End If If CheckBox2.Value = True Then sheets1.Cells(Rows.Count, 1).End(xlUp) = "い" End If If CheckBox3.Value = True Then sheets1.Cells(Rows.Count, 1).End(xlUp) = "う" End If If CheckBox4.Value = True Then sheets1.Cells(Rows.Count, 1).End(xlUp) = "え" End If If CheckBox5.Value = True Then sheets1.Cells(Rows.Count, 1).End(xlUp) = "お" End If としました （実際は、CheckBoxの名前が1～5と数字ではないので、ｆｏｒは使いませんでした） すると、 CheckBox1～CheckBox5を全てクリック（ｔｒｕｅ）しても、Ａ１に“お”が反映されるだけで“あ”～”え”が入力されません どうすれば、思い通りになるのでしょうか 初歩的な質問だと思うのですが、よろしくお願いします
- 締切済み
- Excel（エクセル）
- VBA 変数について
VBA初心者でございます。 VBAでgrpという変数を設定し、それをキーにしてオートフィルタをしたいです。 以下のコードではエラーがでてしまうのは、なぜでしょうか？ どうぞ宜しくお願いいたします。 Sub 絞り込み2() Dim grp Set grp = Worksheets("リスト").Cells(3, 2) Worksheets("マスタ0701").AutoFilterMode = False With Worksheets("マスタ0701").Range(Cells(3, "B"), Cells(Rows.Count, "CK").End(xlUp)) .AutoFilter Field:=13, Criteria1:=grp '.CurrentRegion.Select Range(Cells(3, "B"), Cells(Rows.Count, "CK").End(xlUp)).SpecialCells(xlVisible).Copy Worksheets("検索結果").Range("A1") '.AutoFilter End With End Sub
- ベストアンサー
- Visual Basic
- VBAについて質問です。
VBAについて質問です。 まとまったデータがあるところから検索したい月及び各項目のデータを検索し、項目シート事に抽出するという作業を行なっています。そこで問題がでました。6月にはデータはあるが、5月にはデータはない。 そうすると以下のコードの場合デバックが入り、他の検索が出来ません。 どうしたらよいのでしょうか？ 分かる方がいらっしゃいましたらどうかお願い致します。 'シートの変更 Range("B30").Select Sheets("東京").Select '●Sheet2書込み行 Sheets("東京").Range("A5").CurrentRegion.Clear Sheets("東京").Range("A5:F5").Value = _ Array("依頼書No.", "受付日日", "担当者", "枚数", "工数", "備考") Row2 = 5 For R = 2 To Sheets("日報").Cells(Rows.Count, "A").End(xlUp).Row If Sheets("東京").Range("A2") = Sheets("日報").Cells(R, "A") And _ Sheets("東京").Range("B2") = Month(Sheets("日報").Cells(R, "C")) Then Row2 = Row2 + 1 Sheets("東京").Cells(Row2, "A") = Sheets("日報").Cells(R, "B") Sheets("東京").Cells(Row2, "B") = Sheets("日報").Cells(R, "D") Sheets("東京").Cells(Row2, "C") = Sheets("日報").Cells(R, "F") Sheets("東京").Cells(Row2, "D") = Sheets("日報").Cells(R, "I") Sheets("東京").Cells(Row2, "E") = Sheets("日報").Cells(R, "K") Sheets("東京").Cells(Row2, "F") = Sheets("日報").Cells(R, "L") End If Next R '●抽出結果を日付で並べ替え If Row2 = 5 Then MsgBox "該当データなし！" Else Sheets("東京").Range("A5:F" & Row2).Sort _ Key1:=Range("B6"), Order1:=xlAscending, _ Header:=xlYes, OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom, SortMethod:=xlPinYin End If Sheets("東京").Select Range("B5:B200").Select Selection.NumberFormatLocal = "yyyy/m/d" Rows("5:5").Select With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlBottom .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .MergeCells = False End With このあとに式を入れて、罫線を入れてコピーして、というコードが入っています。 どうぞ宜しくお願い致します。
- ベストアンサー
- その他MS Office製品
- エクセルVBA 二回目の処理でエラーが。。
お世話になります。 今回は下記のコードですが、一回目の処理は実行されますが 二回目になるとエラーになります。 Findが悪さしているのは分かっているのですが、解決方法が分かりません。 宜しくお願い致します。 '// 非対象相手先にチェックを付ける作業 With Sheets("非対象") For Each CRR In .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row) If CRR = TG Then Serc = .Cells(CRR.Row, 2) Cells(Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row).Find(Serc).Row, 36) = 0 End If Next End With 変数の宣言はちゃんと出来てます。 一回目のForではエラーも出る事なく実行されます。 しかし、二回目ではWithが設定されてない的なエラー表示がされます。
- ベストアンサー
- Visual Basic
- VBAで列を探して最終行までハイパーリンクを付けた
Ａ列に URL http://oshiete.goo.ne.jp/ http://oshiete.goo.ne.jp/ http://oshiete.goo.ne.jp/ http://oshiete.goo.ne.jp/ http://oshiete.goo.ne.jp/ http://oshiete.goo.ne.jp/ http://oshiete.goo.ne.jp/ http://oshiete.goo.ne.jp/ http://oshiete.goo.ne.jp/ が入ってる場合は、 Sub Sample1() Dim R As Range For Each R In Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).Row) ActiveSheet.Hyperlinks.Add Anchor:=R, Address:=R.Value Next End Sub でハイパーリンクを付けられますが、 URL列がＡ列じゃない場合の応用の聞かせ方がわかりません。 場合によっては、画像のようにＢ列であったりＣ列であったりします。 なので Dim C As Long C = Cells.Find(What:="URL", LookAt:=xlWhole).Column で列番号を取得して、ループさせようと思ったのですが、 For Each R In Range("a2:a" & Cells(Rows.Count, "a").End(xlUp).Row) の部分でどうすればいいのかわかりません。 range表記ではなくcells表記にすればいいような気がしますがどうやって改造すればいいでしょうか？ あと、この場合はForEachではなくDoloopやForNextを使った方が良いのでしょうか？
- ベストアンサー
- オフィス系ソフト
- VBA データのある最終行の取得
エクセルVBAで最終行を取得する良い方法を教えて下さい。 VBA初心者でいきづまっていまして、、、 内容はボタンを押した時にF列に対して最終行を取得して、 その最終行の下のセルにボタンの内容が繁栄させるようにしたくて Private Sub CommandButton202_Click() Dim lastRow As Long lastRow = Cells(Rows.Count, 6).End(xlUp).Row + 1 Cells(lastRow, 6).Value = CommandButton202.Caption End Sub と作ってみたのですが、F17より下に文字がはいっているため、 検索範囲を「F列」ではなく、「F2：F16」のように範囲を指定したいのですがどうしたらよいでしょうか、、、。 結構色々調べてはみたのですができなくて、、、
- 締切済み
- その他（プログラミング・開発）
お礼
沢山の情報ありがとうございます！(^－^)Rang("B2").CurrentRegionでA列まで取得できるというのは意外です！今度自分でもテストで試してみようと思います。また、CurrentRegionが保護シートでは使えないとは知らなかったです…！色々と教えていただき、ありがとうございました！