最終行取得。 【落とし穴に注意】VBAで最終行の行番号を取得する

[EXCEL]最終行・最終列へセルを瞬間移動する(動画あり)|EXCEL屋(エクセルや)

最終行取得

この記事の目次• 最終行を取得するfunctionを作る 冒頭でも書きましたが、最終行を取得するには、様々な方法があり、状況により差し替えたい場合も出てきます。 そのため、最終行を取得するロジックは、functionとして独立させてしまいましょう。 function化することで、最終行を取得する方法を簡単に変更できるようになります。 変更したいときには、このfunctionの内部だけを変更すれば済むようになるのです。 functionのひな形 私は、最終行を取得するfunctionを、次のような感じで定義をしています。 最終行を取得したい ワークシートのworksheetオブジェクト• 最終行の判定に使う 列番号 を指定すると、最終行の行番号が得られるというfunctionです。 列番号は省略することができ、指定しなかった場合には「1列目」が自動で指定されます。 functionの呼び出し方 このfunctionを呼び出したいときには、次のような感じで呼び出します。 1.RangeオブジェクトのEndプロパティを使う方法(先頭行から下に検索) 最初の方法は、RangeオブジェクトのEndプロパティを使う方法です。 Cells 1, CheckCol. End xlDown. Row End Function このようにすると、指定した列(列指定を省略した場合は1列目)の1行目から、表の下端まで移動し、その行番号を取得することができます。 また、オートフィルタや非表示行がある場合もうまく動作しません。 2.RangeオブジェクトのEndプロパティを使う方法 その2(最終行から上に検索) 2番目の方法は、最終行から上に、RangeオブジェクトのEndプロパティを使って検索をする方法です。 Cells WS. Cells. Rows. Count, CheckCol. End xlUp. Cells. Cells. Rows. 先ほどと違い、表の中に空行があっても正しく最終行を取得することができます。 欠点 オートフィルタや非表示行がある場合もうまく動作しません。 たとえば、1行目~5行目まで、データが入っていても、4行目・5行目が非表示になっていると、次のように最終行を正しく取得できません。 3.WorksheetオブジェクトのUsedRangeを使う UsedRangeを使うと、使用済みの最終行を取得することができます。 UsedRange. UsedRange. Rows. Count - 1 End Function WorksheetオブジェクトのUsedRangeプロパティで、使用済みの領域の情報を得ることができます。 そこで、UsedRangeを使って、最終行を取得します。 正常に動作しないといっても、必ず 最終行よりも大きい数字を取得できますので、これで実害がないようであれば、UsedRangeを使う方法で構いません。 ただし、値が入っている最終行を完璧に求めるためには適していません。 4.UsedRangeの結果に補正を加える方法 UsedRangeの結果に補正を加えて、どんな場合でも最終行を取得する方法を紹介します。 UsedRange. UsedRange. Rows. Cells C, CheckCol. Cells C, CheckCol. つまり、このForループでは、• UsedRangeで得られた「行」から• 上に1行ずつ移動していき• 空欄ではないセルが見つかったら• そのセルの行番号をgetLastRowに代入する という動作をしていることになります。 これで、非表示セルが混じっていたとしても、最終行を取得することができるのです。 欠点 この方法は、forループの中で、1つ1つセルの内容を確認していくため動作速度が若干遅くなる場合があります。 動作速度を速くするためには、配列を使って、セルの内容を一気に読み込むなどの工夫が必要です。 5.UsedRangeの結果に補正を加える方法 その2 先ほどの「動作が遅い」という欠点を補うと次のようになります。 UsedRange,. UsedRange. UsedRange. Rows. Range. Cells 1, CheckCol ,. Cells LastRow, CheckCol. Cells 1, CheckCol.

次の

Excel VBAで配列の要素数を取得する:UBound

最終行取得

表内部(3行目以降)のデータ件数をcounta関数で取得します• 「row A2 」という関数で列タイトルが表示されているA2セルの行数(=2行目)を取得します この2つを足すことで、最終行の行数が得られます。 これで、表の外部への空欄挿入や文字入力などにも柔軟に対応できるようになります。 ただし、今度はcounta関数がA行全体ではなく、A3セルからA9999セルまでしか見てくれません。 ですから、約10,000行を超える大きさの表や、表内部で挿入・削除を繰り返すと不具合が出る可能性があります。 作業列を使う方法(途中空欄可) 計算式 作業列を使ってもいいという場合には、A列に空欄があっても比較的平易な方法で、最終行の行番号が計算できます。 解説 こういう、入れ子が何重にもなっている計算式の場合は、入れ子の内側から考えていくのがポイントです。 これを順番に考えていきます。 普通は「len A1 」というような形だと思うのですが、今回は「len A1:A999 」というように、lenの内側が「A1:A9999」と範囲指定されています。 これは、配列数式と呼ばれる形で、 「len A1 」~「LEN A9999 」の9999個の計算を同時に行う という意味になります。 実際にはC列を使うわけではなく目に見えないところで計算されます。 通常のかけ算と違うのは、「あ」と「い」は、両方とも配列数式であるという点です。 この場合には、各行の、それぞれの「計算結果」ごとにかけ算を行います。 かけ算の場合に、• 実際にはD列を使うわけではなく目に見えないところで計算されます。 「え」:index 「う」,0 先ほどの9999個の計算結果を「配列」に変換する計算式です。 意味がわかりにくいと思うのですが、先ほどの9999個の計算結果を、次のmax関数で使うために「ひとまとめにする」一種のおまじないと考えてください。 「お」:max 「え」 先ほどの「う」で計算した9999個の計算結果の「最大値」を計算します。 「う」のステップでは、データが入力されている場合の「行番号」を計算していました。 ですから、その最大値を取ることで、 データが入力されている「最終行の」「行番号」が得られることになります。 計算過程を見ると、先ほどの「作業列」を使った場合の計算と全く同じだ、ということがわかると思います。 注意点 この計算式では「A列全体」という指定ができません。 今回の例でいえば、A1~A9999セルというように 最終行を9999行に指定しています。 逆にいうと、指定したよりも表が大きくなってしまうと正しい計算ができませんので、ご注意ください。 match関数を使う方法(最終行が数値。 途中空欄可) ここからは、特定の条件を満たす場合に、上で紹介した計算式よりも簡便な計算式で、最終行の行番号を求める計算式を紹介します。 計算式 A列の最後のデータが数値の場合には、次の計算式で最終行の行番号が計算できます。 解説 match関数は、「目的の値」を「指定した範囲」から探して、その位置を返す関数です。 指定した範囲に「A:A」と指定している場合には、match関数で得られる「位置」は「行番号」と一致します。 つまり、この計算式では、• 「A列の中で最大の数よりも1」大きい数を• A列から探して• 該当するデータの行番号を返す という計算式を入れているわけです。 元表よりも「大きい値」を探すとどうなる? 今回の例では「A列の中で最大の数」は11です。 それよりも1だけ大きい数は「12」。 ですから、今回の計算式では「12」という数字をA列から探すことになるわけです。 ・・・ってそんなのA列にあるわけないですよね? なぜなら、A列に入っているデータの最大値が11ですから、12という数字は 絶対に存在していません。 実は、match関数の3つ目の引数で「1」を指定していると、このような場合には 指定範囲の「一番下」の行の行番号を返してくれるのです。 match関数で3つ目の引数を「1」にしたときの動作 match関数の3つ目の引数を「1」に指定すると、• 指定した数と一致するか、それより小さい数の中で最大の数を探す という動きをします。 実際には、エクセルは、次のような処理を行います。 1.A10セルに最大の値が入っていると 誤解する A3セル~A10セルの中で一番大きい数値は「A10」セルに入っているはずとエクセルは考えます。 2.「A10セル」の値を、指定した数と比較する A10セルに入っている(その最大であるはずの)値「9」を調べると、指定した数「12」よりも小さいことがわかります。 そのため、エクセルは、A10セルの「9」は、A列のデータの中で「最大のデータ」だと誤解してしまいます。 先ほどの計算式と似ていますが、• 1つめの引数が「""」(=空欄)• 解説 今回の計算式を理解するポイントは、次の2つです。 1.3つめの引数を「-1」にしたときの挙動 3つ目の引数を「-1」にすると、先ほどとは違い、• 指定した数と一致するか、それより 大きいデータの中で最小のデータを探す という動きをします。 2.文字列データの大小関係 エクセルの世界では、文字列データを比較した場合には、 (空欄)<(何か値が入っているデータ) という関係が成り立ちます。 この2つの前提知識が理解できれば、あとは、先ほどの話と一緒です。 具体的には、• そのため、A3セル~A10セルの中で一番 小さいデータが「A10」セルに入っているはず、とエクセルは誤解します。 A10セルに入っている値「YMA」を見ると、指定した値「 空欄 」よりも「大きい」と判断します。 結局、この表の中で一番「小さいデータ」であるはずの「YMA」でさえも、指定した値よりも「大きい」と判断されてしまうのです。 ということは、A10セルの「YMA」が、 指定した数「(空欄)」「より大きいデータの中で最小」と判断されるということでもあります。 その結果、match関数で、一番下のデータの行番号「10」が得られるのです。 注意点 この方法は、表の一番下のデータが「文字列」である場合にだけ使用可能です。 下記の例のように、表の一番下のデータが「数値」データだと、正しい結果が得られませんので、ご注意ください。 厳密には、一番下のデータが「論理値」(=TRUEまたはFALSE)の場合も正しい結果が得られませんが、実務上は問題ないことがほとんどでしょう。 match関数を使う方法(最終行は任意。 途中空欄可) 先ほどの2つを組み合わせると、数値と文字列が混じっているデータでも、最終行を取得することができます。 要するに、• 計算式 エラー処理なども加え、具体的に計算式化すると、次のようになります。 このようにして、任意の列のデータを表示させることができます。 まとめ このように、最終行の行番号・内容を取得する方法は、非常に複雑です。 冒頭でも書いたとおり、できれば、最終行を取得する必要がないようなレイアウトの表を作ることをおすすめします。 もし、どうしても最終行の行番号・内容を取得しないといけない場合には、 1. 他人が見てわかりやすい計算式にしたい場合 作業列を使う方法 2. 最後のデータの種類がわかっていて かつ 計算式の短さを追求したい場合 match関数を使う方法 3. 実行速度を追求したい場合 match関数を使う方法 4. その他 配列数式を使う方法 を使って頂くといいのではないかと思います。

次の

【Excel VBA】最終行を取得する方法!空白を含むデータにも対応

最終行取得

getRange "B:B". filter String. length; getLast系はちょっと使いにくい・・・ getLast系のメソッドは、シート全体からの最終行および最終列しか取得できないところが融通悪し、です。 getRangeなどで範囲を指定してから探索させようとすると、getRangeの範囲の最終行を返してきます。 欲しい値は、getRangeの中で、データの存在する最終部分なのですが、どうやら仕様上、ダメなようです。 (試しにやってみたら、なんかgetRangeで指定した範囲の最終行をそのまま返してくるようになってしまい、データの存在する最終行が分からず、です。 ) VBAのxlUpやxlDownみたいなのがあれば良いんですが、残念・・・。 ループさせたりして頑張ると、なんか取得できるらしいんですが、次項で紹介する方法のほうがスマートだったので、断念してしまいました。 (getLast系メソッドの特徴を考慮すると、これらを使う場合は、そのシートをパラメータ専用にしておいて、A1部から値を入力させる設計にしておくと便利かと思います。 ) 指定した範囲の最終行列数が欲しい時はコレ 先ほどのソースコードのmemo03部分。 filterとlengthを使った方法だと、指定した列(指定した範囲)の中における、データの存在する最終行列の数を取得してくれるので、大助かりです。 この方法は、下記の記事様に紹介してあり、本当に助かりました。 まとめ Google app scriptでは、パラメータ専用のシートであれば、getLast系メソッド。 指定した範囲の最終値が欲しい時は、filterとlengthが良いかと思います!.

次の