- 締切済み
VBAのxlDownと同じ昨日の関数
kagakusukiの回答
- kagakusuki
- ベストアンサー率51% (2610/5101)
文字の場合、通常は =MATCH("*",A:A,-1) で最終行を求める事が出来るのですが、例えば「 ABC」等の様に頭文字が空白スペースとなっている文字列データが、最終行にあった場合には、正しい最終行を求める事が出来ませんし、頭文字が空白となっている文字列データが、最終行よりも上の行に存在していた場合には#N/Aエラーとなってしまうという難点があります。 又、空白の場合だけではなく、頭文字が半角の「♂」や「♀」、「!」、「#」、「%」、「(」、等々となっている文字列の場合も同様です。 ですから、完璧を期するなら、 =MATCH(CHAR(1),A:A,-1) とした方が良いかも知れません。 一方、数値の場合には10000000000よりも大きな数値が入力される可能性も無いとは言えませんから、MATCH関数の検索値を10^10としたのでは不十分だと思います。 Excelで扱う事の出来る最大の数値は「2^1024」よりも1小さい値までなのですから、MATCH関数の検索値として「2^1024-1」が設定出来れば良いのですが、Excelでは「2^1024」を数値として扱う事が出来ませんので、「2^1024-1」の前半部分の「2^1024」が数値として認識されないために、「2^1024-1」という計算が行われず、MATCH関数の検索値として「2^1024-1」を指定した場合には#NUM!エラーとなってしまいます。 ですから、数値の場合には、 =MATCH(1.99999999999999*2^1023,A:A) 或いは =MATCH(9.99999999999999E+307,A:A) 或いは =MATCH(MAX(A:A)+1,A:A) 等の様にされた方が良いと思います。 実用的には、 =MATCH(9E+307,A:A) でも十分です。 そして、数値データと文字列データが混在している場合には、 =MAX(IF(COUNT(A:A),MATCH(9E+307,A:A),0),IF(COUNTIF(A:A,"*?"),MATCH(CHAR(1),A:A,-1),0)) になります。（IF関数を使ってエラー回避を行いませんと、範囲内に数値データか文字列データのいずれか一方でも存在していない場合には、#N/Aエラーとなってしまいます） 尚、回答No.1様の方法や、この回答の上記の方法は「A列の中でデータが存在する最終行」を求める方法になりますから、A10よりも下にあるセル、例えばA11セルにデータが存在していた場合には、「A5からA10までの範囲内で初めて空欄セルが存在する一つ上のセルの行番号」ではなく、「11」という値が返されてしまいます。 ですから、「A5からA10までの範囲内で初めて空欄セルが存在する一つ上のセルの行番号」を求める場合には、次の様な関数となります。 =MAX(IF(COUNT(A5:A10),MATCH(9E+307,A5:A10),0),IF(COUNTIF(A5:A10,"*?"),MATCH(CHAR(1),A5:A10,-1),0))+ROW(A5)-1 尚、A5からA10までの範囲内にデータが全く存在せず、全て空欄のセルばかりとなっている場合には、「A5からA10までの範囲内で初めて現れる空欄セル」はA5セルなのですから、上記の関数では「A5セルの1つ上のセルの行番号」である「4」が返されます。 ついでに言いますと、VBAで.End(xlDown).Rowとした場合、必ずしもデータが存在する最終行の行番号が求められるとは限りません。 完全に確認出来ている訳では御座いませんが、どうやら.End(xlDown).Rowで最終行を求めようとした場合には、もしも、データが存在する最終行よりも下の行に、ワークシート関数の計算処理の結果として「""」（空欄）となっているセルが存在している場合や、「過去において一度でもデータが入力された事があるセル」が存在していた場合などには、「データが存在する最終行の行番号」ではなく、「過去において一度でもデータが入力された事があるセルの行番号」が返される様です。
関連するQ&A
- 関数による空白行を除き範囲を選択するマクロ
添付ファイルのシートが同じファイル内にある別シートの値を参照の上、元シートの入力がない場合は、if関数により空白表示する様にしてあります。（関数自体は1万行くらいまで入力すみです。） このシートの空白表示以外のセル（A6:i22)までを選択するマクロを組みたいのですが普通のrange(A6:i6).End(xlDown).Rowを使うと空白表示行を含めた関数のある最終行を選択してしまいます。 サンプルの（A6:i22)をうまく選択するマクロを教えてください。
- 締切済み
- Excel（エクセル）
- 関数で最終行を求めるにはどうすればいいでしょう
A1からA5・A10に値が入ってるのですが VBAを使わずに関数で最終行を求めるにはどうすればいいでしょう？ A10が最終行なので「１０」が返ってほしいです。 空白行もあるのでCOUNTAだとダメみたいです。 COUNTA関数+INDEX関数を使えば求められるという情報を見ましたが使い方がわかりません。
- ベストアンサー
- オフィス系ソフト
- エクセルVBAについて
こんにちわ！ 今、エクセルでAシートの入力した項目をBのシートへデーターが入力できるようなシステムを以下のようにくみました。 そこでBシートにデーターが入力されるのですが２０行まで入力すると入力できないようにしたいのですが、なかなか上手くいきません。 A1からF20まで書式のロックを外しそれ以外のセルは保護をかけたのですがその状態でVBAを使って２０行以上入力できませんという感じのエラー表示をしたいのですが、どうすればいいでしょうか？ VBAは初心者ですが宜しくお願いします。 Private Sub CommandButton1_Click() Dim row As Integer row = WorksheetFunction.CountA(Sheets("date").Columns(1)) + 1 Sheets("date").Cells(row, 1).Value = Range("B2").Value row = WorksheetFunction.CountA(Sheets("date").Columns(2)) + 1 Sheets("date").Cells(row, 2).Value = Range("B3").Value row = WorksheetFunction.CountA(Sheets("date").Columns(3)) + 1 Sheets("date").Cells(row, 3).Value = Range("B4").Value row = WorksheetFunction.CountA(Sheets("date").Columns(4)) + 1 Sheets("date").Cells(row, 4).Value = Range("B5").Value row = WorksheetFunction.CountA(Sheets("date").Columns(5)) + 1 Sheets("date").Cells(row, 5).Value = Range("B6").Value row = WorksheetFunction.CountA(Sheets("date").Columns(6)) + 1 Sheets("date").Cells(row, 6).Value = Range("B7").Value Sheets("統制入力").Select Range("B17").Select ActiveWindow.SmallScroll Down:=-9 Range("B3:B7").Select Selection.ClearContents Range("B1").Select End Sub
- ベストアンサー
- Visual Basic
- 空白セルをデータと認識してしまいます
Excel2003を使用しています。 「マクロの記録」を使って、あるリストを自動作成 しようとしているのですが、うまくいきません。 Ａ，Ｂ２つの列に分かれたデータを連結させるため、 Ｃ列に「CONCATENATE」関数を入れて適当な行まで コピーします。（それ以上はデータが入らないだろうと思われる１００行目ぐらいまで） その後Ｃ列をコピーし、「値貼付け」をして 関数を値に変えます。 そうすると、たとえば実際には２０行目までデータが 入っていたとすると、２１行目～１００行目までは 空白になりますよね？ でも、どうもその空白行もデータとみなされている ようなんです…。 Range("C2").End(xlDown).Row で下端セルを探そうとすると、１００行目に なってしまいますし、「ジャンプ」で空白セルを 探しても、同じ結果になります。 空白セルを選んで一度「Delete」を押すと 正しく「空白」と認識するようなのですが…。 「実際にデータの入っているセルとそうでないセル」 を自動で判別させたいので、 簡単に「何も入っていないセルを”空白”と正しく 認識させる方法」はありませんでしょうか？
- ベストアンサー
- オフィス系ソフト
- エクセルVBAで関数が入った空白セルの取得
エクセルVBAで下記のようなセルの参照をしたいのですが、よろしくお願いします。（エクセル２００２です） A B C D E F １ 78 80 セル（A1）（B1）には値も関数も入れられています。 セル（C1）（D1）（E1）には値は入っていませんが、ワークシート関数が組み込まれており空白となっています。（関数の""にて空白） セル（F1）には何も入力されていません。 今回はセル（B1）の80の値を取得できるようにVBAで下記のようにコードを組みました。 Range（"F1").End(elToLeft).Activate そうすると、セル（E1）に関数があるために（？）（E1）がアクティブな状態となります。 関数が入力されていても空白として（B1）をアクティブな状態にするために良い方法はありますでしょうか？ 誠に申し訳ございませんが、よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- エクセル/指定範囲のセル数を返すワークシート関数は？
エクセル2000です。 たとえば、A1:A100の範囲に、空白か否かを問わずセルがいくつあるかを返すワークシート関数はないでしょうか？ 空白以外の数＋空白の数で求められるかと思いましたが、 =COUNTA(A1:A100)+COUNTBLANK(A1:A100) では、計算の結果 ="" になっているセルが両方にカウントされてだめです。 VBAなら Range("A1:A100").Count で一発なのですが・・・・。
- ベストアンサー
- オフィス系ソフト
- VBAのセル空白探しは？？？？？？多々仕様なども含めて迷っている部分があるので助けてください
VBAで入力チェッカーを作っているのですが、仕様の部分でなやんでいます。 Private Sub CommandButton1_Click() Dim i As Long lastRow = Range("A65536").End(xlUp).Row For i = 10 To lastRow If Range("A" & i).Value = "" Then Range("A" & i).Value = ??? Exit For End If Next i End Sub USERがあるセルにデータを入力するとA列に５行ごと自動に値が入ります。 しかし、入力忘れで５行飛ばしたりはたまた１０行空白が出来る場合があります。 その際のチェッカーの役割なのですが。。。 したから行を見ていってA１０～最終行までの間でどこか空白があったら下から空白を探し空白箇所から5個上のセルの値を基本的にコピーをしていれる。 もし5個上のセルにも無かったら10個上みたいにやりたいのですがどうしたらよいでしょうか？
- ベストアンサー
- Visual Basic
- EXCELマクロのデータの最終行の取得
EXCELマクロでRange("A1").End(xldown).Rowで、正しい値が取得できません。 A1からA6697まで、連続して入力しているので、途中に空白セルはありません。 それよりうしろにも、データはありません。 しかし、帰ってくる値はなぜかそれより遥か下の空白セルの「78889」です。 下からxlUpで検索しても同じ値が返ってきます。 A78889には、何も入力してありません。 rangeで範囲指定ではなく、usedrangeやcolumnsでみると正しく認識します。 どなたか分かる方いらっしゃいましたら、よろしくお願いします。 ※A1からA6697まではマクロで自動作成して埋めているセルなので、問題のセルを含めそれ以外の場所はなにも触っていません。
- 締切済み
- オフィス系ソフト
- 最終行の取得について
今回、最終行を習得するにあたって壁にぶちあたってしまいました。。 まず、元dataがありましてそれを Ｂ１セル=IF($A1=0,"",A1)’下方向にペースト といったように数式を用いて空白にしたところ、 それを空白としてひらい出すことができず、別のシートに値のみ貼り付けを行っても、うまくいきません。現状このようなコードで最終行をとらえれません、何かいい方法がありますでしょうか？ Range("Ｂ１").End(xlDown).Row
- 締切済み
- オフィス系ソフト