[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『最終行確認の作法』（半平太）
『結合されたセル 別シート数式をコピーしオートフ・・』（ぬた） の派生トピです。 [[20190510203545]]
チオチモリンさんへ
＞ >Cells(Rows.Count,1)End(xlUp).Row ＞ この書き方は好きな人も多いですが私はほとんど使いません。 ＞ 状況依存のコードだからです。
ちょっと驚きの見解なんですが、 チオチモリンさんが常用する「最終行確認方法」がどんなものなのか、教えていただけませんか？
（半平太） 2019/05/12(日) 16:18
アナクロですが目視(Ctrl+{end})でしょうか。
コードで最終行を確認することはほとんどありません。
UsedRange後のSpecialCells(xlCellTypeLastCell)や
最終行にダミー行を設けてた上での名前の利用とかもケースバイケースで。
Cells(Rows.Count,1).End(xlUp).Row の場合は、手操作の結果と同じセルが得られますから 何かしらの入力値があることが前提ですが もう一つ、そのセルが非表示でないことも前提ですよね。 最終行がフィルタやアウトラインなどで非表示になっていれば異なる結果になりますから。 _____ ____ _____ ____ |_____|__A_| |_____|__A_| |___1_| 1 |___1_| 1 |___2_| 2 |___2_| 2 |___3_| 3 |___6_| |___4_| 4 |___7_| |___5_| 5 |___8_| |___6_| |___7_| |___8_|
レスの回答の場合は、暗黙の了解でそれはない。ということかもしれませんが、本来は Cells(Rows.Count,1).End(xlUp).Row を使う場合には行のVisibleを確認する必要があるのではないかと思います。 で、非表示の確認と非表示の場合の対応のコード化は面倒という考え（実務の場合は必須？）です。 失敗したときUndoききませんし。
まあ なますを吹いているのかもしれません。 （チオチモリン） 2019/05/13(月) 00:28
今回は、最終行をコードでどう確認するかに絞りたいです。
＞ UsedRange後のSpecialCells(xlCellTypeLastCell)や
これって、列単位で求められるんですか？
いま、これでテストしたら、他の列の最下行が出て来たのですが・・(そっちの列が、より下にデータがある場合）
↓
Debug.Print Range("A:A").SpecialCells(xlCellTypeLastCell).Row
＞ 最終行にダミー行を設けた上での名前の利用とかもケースバイケースで。
これはダミー行を削除されたら終わりですよね？
状況依存と言えなくないですか？ その名前定義が存在していることをコードで確認していれば別ですが。
まぁ、存在しても、どこかに移動されたり、その下にデータを書き込まれたりすると 終わりであることに変わりはないです。
＞ Cells(Rows.Count,1).End(xlUp).Row ＞ の場合は、手操作の結果と同じセルが得られますから ＞ 何かしらの入力値があることが前提ですが
これは、私も不安に思うときがあります。 データが無かったら実行しないから、まぁいっかぁ・・と割り切っています。
あと、データが最下行まで一杯に埋まっているケースも、頭をよぎりる時がありますね。
＞ Cells(Rows.Count,1).End(xlUp).Row ＞ の場合は、手操作の結果と同じセルが得られますから ＞ 何かしらの入力値があることが前提ですが ＞ もう一つ、そのセルが非表示でないことも前提ですよね。 ＞ 最終行がフィルタやアウトラインなどで非表示になっていれば異なる結果になりますから。
これはおっしゃる通りですが、個人的には無視しています。 当該データシートの使い方に、そんなケースが予想されるかどうかで決する。 正に思い込みの満杯の状況依存ですけども。
他の方は、実務でどこまで慎重にやっているんですかねぇ。
私は実務なんて無いから、無縁なんですが、 「簡単プログラミング」の大村あつしさんも .End(xlUp)を使っていたけどなぁ・・
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
いま、上の関係でUsedRangeプロパティをテストしていたのですが、
（3）と(4)は「プロパティの使い方が不正です」となります。
Sub test()
Dim a As Object
Dim b As Worksheet
Set a = Me
Set b = Me
Sheets(Me.Name).UsedRange '(1)
a.UsedRange '(2)
b.UsedRange '(3)
Me.UsedRange '(4)
End Sub
横着せずに、返ってくるRangeオブジェクトをSetで取得すれば問題ないのですが、 多分そんなことはやってないですよね？
するとチオチモリンさんは、(1)系のステートメントで、最新状態にしているんでしょうか？ 何か、凄く面倒な気がしてきているんですが。
（半平太） 2019/05/13(月) 10:18
まずはお礼 訂正の組み込みありがとうございます。 （ちょっと恥ずかしかった。）
> ＞ UsedRange後のSpecialCells(xlCellTypeLastCell)や > これって、列単位で求められるんですか？ ご承知の通り、列単位で求めるものではないです。
>最終行にダミー行を設けた上での名前の利用
これはダミー行を削除されたら終わりですよね？
その通りですが、自分で使う分には削除はしません。
※ データの追加は最終行以降に追加ではなくて、
最終行-１（適当）あたりに必要行を挿入してから行います。
※２ ダミーを入れるのは、主に手操作での{Ctrl}+{↓}でのストッパーとしての役割が大きいです。
>ケースバイケース と書いたのは、自分の作成したブックで運用する上でのお話です。 自分が必要とする値が得られれば、どの方法でもよい（状況でチョイス）という考えです。
>Cells(Rows.Count,1).End(xlUp).Row ↑の方法を否定するつもりはないです。
>当該データシートの使い方に、そんなケースが予想されるかどうかで決する。 自分でやる分にはシートの状況が分かりますが レスの場合は当該データシートは見えませんので、（多分）の文字が頭をよぎります。
実務の場合ですが、私の場合は ・ 新規シートを作成してそこに読み込み、その後コピーアンドペースト(値） ・ ActiveCellを基準として読み込む位置を決定 でしょうか。 コーディングは趣味のレベルですから、100％想定通りの結果になるという確信（幻想）は持っていません。 確実なのが一番と考えます。
UsedRangeですが お呪い的に ActiveSheet.UsedRange とかです。
>(1)系のステートメントで、最新状態にしているんでしょうか？ > 何か、凄く面倒な気がしてきているんですが いえ、もっと面倒なことをやってます。
昔ファイルサイズが異様に大きいブックがありまして、原因を調べる内に最終セルがとんでもない位置になっていることがわかりました。 手作業で不要行列を削除して保存すると小さくなるけど、いつの間にか又 ↑ のようになる。 原因を追究するも不明で、対症措置として Shape、空白、罫線、結合セルなどをチェックして最終セルを判定（自分基準での決定）。不要行列を削除後にUsedRangeをかけるようにしました。 今ではよく使うマクロに↑の処理を組み込んでいますので、意識することはないです。
こんなところですが、参考にもならないお話で恐縮です。
（チオチモリン） 2019/05/13(月) 12:26
派生元を熟読してないので、的外れな意見だったらゴメンナサイ。
「最終レコード」ということであれば、私の場合、 AutoFilter.Rangeを基準に考えて操作したりしてます。 改めて考えてみれば、手持ちのファイルの中には、 この為だけにAutoFilterを設定してあるシートが結構ありますねぇー^^;
用途からすると[テーブル]ツールを使った方が良いのかも知れませんが、 使い慣れてるAutoFilterを使ってます。 まあCurrentRegionよりは安心して使えるかなー くらいの感覚で。
AutoFilterが解除されてしまったらオシマイな訳ですから、 その辺の対策が悩ましいです。可能な場合はシート隠しちゃいますけど。
（白茶） 2019/05/13(月) 14:17
チオチモリンさんへ
ご回答、ありがとうございました。
基本的なことも分かっていないと、自覚させられます。
白茶さんへ
＞ 「最終レコード」ということであれば、私の場合、 ＞ AutoFilter.Rangeを基準に考えて操作したりしてます。
ここ、どういう操作なのでしょうか？
頭の中で、「最終レコード」と「AutoFilter.Range」繋がらないのですけど、 何をフィルターするんでしょうか？
（半平太） 2019/05/13(月) 17:50
言葉足らずでスミマセン。 レコードを追加していく場所の頭に予めAutoFilterを設置しておくのです。 初期状態では列見出しだけ。という事になります。 そういう設計にしておいて、 例えば
'下にレコードを追加
With Sheets("DATA").AutoFilter.Range
With .Rows(.Rows.Count).Offset(1, 0)
.Value = 新規レコード
End With
End With
'最終行削除(AutoFilter外の範囲も含めて)
With Sheets("DATA").AutoFilter.Range
If .Rows.Count < 2 Then Exit Sub
.Rows(.Rows.Count).EntireRow.Delete
End With
みたいな書き方をしていくというものです。 Filterとしての機能は全然使いません^^; (常にAutoFilterModeがTrueでFilterModeはFalseの状態です)
正直、利点と呼べるものがあるのかと言われると・・・
・空白行さえ回避すれば、途中に多少の歯抜けがあってもレコード範囲を取得できる。 ・CurrentRegionとは違って、上や横に余計なデータが隣接してても、そこは対象から外せる。 ・もしAutoFilterが解除されてたら冒頭でエラーにしてしまえる。(←これは利点なのか? ^^;) ・別のテーブルを1シートに同居させるという(自らの安易な)発想を設計段階で排除できる。 ・テーブルのレイアウト変更への対応が(ちょっぴり)楽になる(場面があったり逆に面倒な事になったり^^;)。
んー。やっぱ大して無いですね・・・orz
（白茶） 2019/05/13(月) 18:54
＞ Filterとしての機能は全然使いません^^; ＞ (常にAutoFilterModeがTrueでFilterModeはFalseの状態です)
CurrentRegionの列限定版ですね。
面白いです・・
たまに、どの列になるか分からないけども、最下行番号を取得したいって時に 通常、各列片っ端から、end(xlup)で調べるなんてことがありますが、 「AutoFilterModeがTrueでFilterModeはFalseの状態」にしてから、 AutoFilter.RangeのRowsプロパティを調べればいい・・
・・と思って試したらダメだった・・orz。
<DATA> 行 __A__ __B__ __C__ 1 Item1 Item2 Item3 2 1 1 3 2 2 4 A 3 5 4 6 5
上図でA:B列の最下行(4行）を取得する・・ことは出来ない(とほほ）
Sub GetLastRowNum()
Debug.Print Range("A1").CurrentRegion.Address ’$A$1:$C$6
With Sheets("DATA")
.AutoFilterMode = False
.Range("A:B").AutoFilter
Debug.Print .AutoFilter.Range.Address ’$A$1:$B$6 ($A$1:$B$4になってくれない）
End With
End Sub
よく読むと ↓ でしたね。 微妙な違い。 ちょっと私は使いこなせないです。
＞ ・CurrentRegionとは違って、上や横に余計なデータが隣接してても、そこは対象から外せる。
（半平太） 2019/05/13(月) 19:43
はは ^^;
初期状態でAutoFilterが設定されている前提のやり方ですからねぇー。 付けたり外したりとか基本的にさせない状況下ですから、 利用できるシーンはかなり限られると思います。
あと、 データを追加する一方ならあまり気にしないでも良いのですが、 実はフィルタ機能を使われちゃうと、 ShowAllData後も最終行が正しく取得出来なかったりする事があるので >とほほ では済まされない場合もあったりして...^^;
>>その辺の対策が悩ましい というのは、そういった所もあります。
（白茶） 2019/05/13(月) 21:32
＞ 付けたり外したりとか基本的にさせない状況下ですから、
そうだったんですか。分かりました。
いずれにしても、面白かったです。(びっくりする方法って、あるもんですね）
ありがとう御座いました。
（半平太） 2019/05/13(月) 22:24
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.