エクセルVBAでデータ最終行取得方法

Question

エクセルVBAでデータ最終行取得方法で良い方法を教えてください。

データの行数、列数は不定。
最多のデータ行の列も不定。

この条件で、データ最終行を取得するにはどうすればよいでしょうか？

lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
では、A列の最終行に限定されます。

lastrow = ActiveSheet.Cells(1, "A").SpecialCells(xlLastCell).Row
では、列の限定はありませんが、一旦データ入力後、削除した部分まで入ってしまいます。

lastrow = ActiveSheet.UsedRange.Rows.Count
では、データ入力後、削除した部分まで入ってしまい、かつ、１行目など上部が空白の場合、不正確になります。

popesyu · Accepted Answer

それぞれの利点も欠点も分かっているなら後は
足りない部分を補うようにすればよいだけではないでしょうか。

・A列の最終行に限定されてしまう。
→列の指定を動的にして最大の行数を取得する。
例えばこんな感じ。ForでなくてもDoとかでも可です。
for x = 1 to ActiveSheet.Usedrange.Collumns.Count
 temprow = ActiveSheet.Cells(ActiveSheet.Rows.Count, x).End(xlUp).Row
 if temprow > lastrow then
lastrow=temprow
 end if
next

結局他の部分にしても
最終行として取得したセル内が空白がどうかのチェックを行い、空白だったら行削除するとか、１行目など上部が空白の場合の例外処理をいれて対応しましょう。

Wendy02 · Answer

こんにちは。merlionXXさん。

With ActiveSheet.UsedRange
　lastrow = .Cells(.Count).Row
End With

＞.Cells(.Count)って、UsedRange内の最後（右下）のセルという理解でよいのですね？

そうです。.Cells は、 左から右へ、上から下へと数を数えていきますから、最後のセルは、右端下になります。

#7 のbanker_Uさんのおっしゃるとおり、私も、SpecialCellsのLastCellは使いたいとは思います。でも、ワークシートに対する　Refreshの方法が見当たらないのです。

最初Clearメソッドを使えばよいと思っていましたが、それだけではダメでした。オブジェクト自体を更新しないとダメなのですね。まだ、明確に、Refreshさせるための方法が見つかりません。ApplicationのWindowやダミーのブックで切り替えるなら出来そうですが、それは、あまりにも泥臭さすぎます。それと、ScreenUpDating ではダメだったわけです。

こんな方法も考えてみました。やはり、前回のような（自称）関西風です。
他にも、Find メソッドで、下から xlPrevious で、戻っていく方法もあると思います。

Sub FindLastRow2()
'最終行を探す
Dim r As Range
Dim MaxRow As Long
Dim ar As Variant
Dim buf As Variant
Set r = ActiveSheet.UsedRange
ar = Evaluate("(" & r.Address & "<>"""")*(ROW(" & r.Address & "))")
For i = r.Rows.Count To 1 Step -1
　　　buf = WorksheetFunction.Index(ar, i, 0)
　　　MaxRow = WorksheetFunction.Max(buf)
　　　If MaxRow > 0 Then
　　　　Exit For
　　　End If
Next
　MsgBox "最後の行は、" & MaxRow
End Sub

KenKen_SP · Answer

#4 です。本当にすみません、、、

#9 で、、

＞#5 です。何度もすみません。

＞#5 だと正しく最終行を返さないので、差替えます。すみません。

は　#4 の間違いです。Wendy02 さん、大変申し訳ありませんでした。以後、気をつけます。

KenKen_SP · Answer

#5 です。何度もすみません。

#5 だと正しく最終行を返さないので、差替えます。すみません。

それから、SpecialCells(xlCellTypeLastCell) は期待通り動作しない
場合があります。それは、ご質問文にあるとおり、

＞一旦データ入力後、削除した部分まで入ってしまいます。

だからです。使う場合は、特に注意が必要ですね。

'Find Last Row Number
Function GET_LASTROWNUM(ByRef SH As Worksheet) As Long
　　
　　Dim rngLASTROWS As Range, R As Range
　　Dim lngLAST_ROWNUM As Long
　　
　　On Error Resume Next
　　Set rngLASTROWS = SH.UsedRange _
　　　　.Rows(SH.UsedRange.Rows.Count).Columns.Offset(1)
　　If Err.Number > 0 Then
　　　　GET_LASTROWNUM = SH.Rows.Count
　　　　GoTo Terminate
　　End If
　　On Error GoTo 0
　　　　
　　For Each R In rngLASTROWS
　　　　lngLAST_ROWNUM = R.End(xlUp).Row
　　　　If lngLAST_ROWNUM > GET_LASTROWNUM Then
　　　　　 GET_LASTROWNUM = lngLAST_ROWNUM
　　　　End If
　　Next R

Terminate:
　　Set rngLASTROWS = Nothing

End Function

'Find Last Column Number
Function GET_LASTCOLNUM(ByRef SH As Worksheet) As Long
　　
　　Dim rngLASTCOLS As Range, R As Range
　　Dim lngLAST_COLNUM As Long
　　
　　On Error Resume Next
　　Set rngLASTCOLS = SH.UsedRange _
　　　　.Columns(SH.UsedRange.Columns.Count).Rows.Offset(0, 1)
　　If Err.Number > 0 Then
　　　　GET_LASTCOLNUM = SH.Columns.Count
　　　　GoTo Terminate
　　End If
　　On Error GoTo 0
　　
　　For Each R In rngLASTCOLS
　　　　lngLAST_COLNUM = R.End(xlToLeft).Column
　　　　If lngLAST_COLNUM > GET_LASTCOLNUM Then
　　　　　 GET_LASTCOLNUM = lngLAST_COLNUM
　　　　End If
　　Next R
　　
Terminate:
　　Set rngLASTCOLS = Nothing

End Function

imogasi · Answer

＃３です。質問の意味が判りました。
Sub test01()
  r = Range("A1").SpecialCells(xlCellTypeLastCell).Row
  c = Range("A1").SpecialCells(xlCellTypeLastCell).Column
  MsgBox r
  MsgBox c
  mc = 1
  mr = 1
  '-----
  For i = 1 To r
  ct = Cells(i, "IV").End(xlToLeft).Column
  If mc < ct Then mc = ct
  Next i
  MsgBox "最右列は" & mc
  '-----
  MsgBox "最下列は" & r
End Sub
で良いでしょう。
LastCellは、質問の意味での最下行を拾うと思う。セルを上から下、左から右に連番を振った最後（のセルのIndex値）だから。
それまでの全行について、データの最右列を探せばよい。

banker_U · Answer

私の案はこんな感じ。

何と言ってもLastCellを使わない手は無いと思う。
その１：
　一旦上書き保存してからLastCellを取得する。
その２：
　Lastcell.Rowからデータの入っている列を探して上へ見ていく方法。

Function LastRow() As Integer
    tempLastRow = ActiveCell.SpecialCells(xlCellTypeLastCell).Row
    tempLastcolumn = ActiveCell.SpecialCells(xlCellTypeLastCell).Column
    EndRowA = Range("A65536").End(xlUp).Row
    LastRow = tempLastRow
    Do While Cells(LastRow, tempLastcolumn).End(xlToLeft).Column = 1 _
            And LastRow > EndRowA
        LastRow = LastRow - 1
    Loop
End Function

KenKen_SP · Answer

> UsedRange で得られる範囲がA1セルからではない場合に備え、
> どのような対応を組み込めばいいでしょうか？

#4 の関数はその点について、対応済みです。

　　Set UR = SH.UsedRange
　　For Each R In UR.Rows(UR.Rows.Count).Columns

の UR.Rows(UR.Rows.Count).Columns の部分で、UsedRange 内の
最下行を表す Columns コレクション（例えばB1:B20) が返されます。

この Columns コレクション 内を For Each ループさせて End(xlUp)
プロパティーで取得しています。

Wendy02 · Answer

こんにちは。Wendy02です。

＞データの行数、列数は不定。
＞最多のデータ行の列も不定。

実際、このようなことに出会ったことがないです。列がどこまで使うという、イメージがないと、実際にどこに行くか分らないことになってしまいます。そして、End プロパティで探していくことになりますね。

＞一旦データ入力後、削除した部分まで入ってしまいます。
ClearContents を使っているようです。そうする、書式データが残っています。Clearを使えば、Format は、削除されますが、画面(Window)を切り替えない限りは、残っています。

なお、これでは、正確にはとれませんが、
lastrow = ActiveSheet.UsedRange.Rows.Count
　　↓
With ActiveSheet.UsedRange
　lastrow = .Cells(.Count).Row
End With

ということです。

まあ、KenKen_SPさんのと比較して、試してみてください。
これは、最終行のみです。（私は、こういうやり方を、関西風と呼んでいます。（^^;）

'------------------------------

Sub FindLastDataRow()
Dim r As Range
Dim myRow As Long
Dim myCol As Integer
Dim i As Long
　Set r = ActiveSheet.UsedRange
　myRow = r.Rows.Count
　For i = myRow To 1 Step -1
　If WorksheetFunction.CountA(r.Rows(i).Cells) > 0 Then
　　 Exit For
　End If
　Next
MsgBox "最終行は、" & i
End Sub

KenKen_SP · Answer

こんにちは。KenKen_SP です。

関数化してみました。折角なので、最終列番号を取得する関数も作って
みました。汎用的に使えると思います。

ロジックは、UsedRange 内の各最終行・列でループさせて最大の行・列
番号をそれぞれ End プロパティーで取得する、、というものです。

基本的には #1 popesyu さんと同一の考え方ですが、UsedRange で得ら
れる範囲は必ずしも A列からではないことに注意が必要です。

UsedRange を使う理由は、調べる範囲（ループ回数）を最小限にして、
高速化するためです。

各関数の引数には Worksheet オブジェクトを渡します。使い方は、
コードの最後の方にサンプルコードを書いておきました。

'Find Last Row Number
Function GET_LASTROWNUM(ByRef SH As Worksheet) As Long
　　
　　Dim UR As Range, R As Range
　　Dim lngLAST_ROWNUM As Long
　　Set UR = SH.UsedRange
　　For Each R In UR.Rows(UR.Rows.Count).Columns
　　　　lngLAST_ROWNUM = R.End(xlUp).Row
　　　　If lngLAST_ROWNUM > GET_LASTROWNUM Then
　　　　　 GET_LASTROWNUM = lngLAST_ROWNUM
　　　　End If
　　Next R
　　Set UR = Nothing

End Function

'Find Last Column Number
Function GET_LASTCOLNUM(ByRef SH As Worksheet) As Long
　　
　　Dim UR As Range, R As Range
　　Dim lngLAST_COLNUM As Long
　　Set UR = SH.UsedRange
　　For Each R In UR.Columns(UR.Columns.Count).Rows
　　　　lngLAST_COLNUM = R.End(xlToLeft).Column
　　　　If lngLAST_COLNUM > GET_LASTCOLNUM Then
　　　　　 GET_LASTCOLNUM = lngLAST_COLNUM
　　　　End If
　　Next R
　　Set UR = Nothing

End Function

Sub SampleMacro()

　　MsgBox "最終行：= " & GET_LASTROWNUM(ActiveSheet)
　　MsgBox "最終列：= " & GET_LASTCOLNUM(ActiveSheet)

End Sub

imogasi · Answer

Sub test02()
d = Range("A65536").End(xlUp).Row
r = Range("IV1").End(xlToLeft).Column
MsgBox d
MsgBox r
End Sub
でやれば途中空白行があっても最下行（行番号）、最右列を取れますが。
ＵｓｅｄＲａｎｇｅ、ＣｕｒｒｅｎｔＲｅｇｉｏｎもそれぞれ特徴は
ありますが。
何が困っているのかよくわからないですがとりあえず。

エクセルVBAでデータ最終行取得方法

それぞれの利点も欠点も分かっているなら後は

こんにちは。

#4 です。

#5 です。

＃３です。

私の案はこんな感じ。

> UsedRange で得られる範囲がA1セルからではない場合に備え、

こんにちは。

こんにちは。

Sub test02()

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング