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

質問

エクセル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
では、データ入力後、削除した部分まで入ってしまい、かつ、１行目など上部が空白の場合、不正確になります。

回答No.1

popesyu · ベストアンサー

それぞれの利点も欠点も分かっているなら後は
足りない部分を補うようにすればよいだけではないでしょうか。

・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

結局他の部分にしても
最終行として取得したセル内が空白がどうかのチェックを行い、空白だったら行削除するとか、１行目など上部が空白の場合の例外処理をいれて対応しましょう。

お礼

なるほど。

ありがとうございます。

回答No.11

Wendy02

こんにちは。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

お礼

何度もありがとうございました。
とても勉強になりました。

回答No.10

KenKen_SP

#4 です。本当にすみません、、、

#9 で、、

＞#5 です。何度もすみません。

＞#5 だと正しく最終行を返さないので、差替えます。すみません。

は　#4 の間違いです。Wendy02 さん、大変申し訳ありませんでした。以後、気をつけます。

回答No.9

KenKen_SP

#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

お礼

何度もありがとうございます。

回答No.8

imogasi

＃３です。質問の意味が判りました。
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値）だから。
それまでの全行について、データの最右列を探せばよい。

お礼

ありがとうございます。

回答No.7

banker_U

私の案はこんな感じ。

何と言っても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

お礼

ありがとうございました。

回答No.6

KenKen_SP

> 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)
プロパティーで取得しています。

回答No.5

Wendy02

こんにちは。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

お礼

ありがとうございました。

With ActiveSheet.UsedRange
　lastrow = .Cells(.Count).Row
End With

勉強になりました！

.Cells(.Count)って、UsedRange内の最後（右下）のセルという理解でよいのですね？

回答No.4

KenKen_SP

こんにちは。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

お礼

わざわざ関数をつくってくださいましてありがとうございます。

UsedRange で得られる範囲がA1セルからではない場合に備え、どのような対応を組み込めばいいでしょうか？

回答No.3

imogasi

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

お礼

ありがとうございます。

ご教示のコードではたとえば、B2:C3のみにデータがある場合、拾うことができません。

回答No.2

bin-chan

> lastrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
> では、A列の最終行に限定されます。
列は最大で２５６なのでループさせ、その中の最大を採用する。

ただし
lastrow = ActiveSheet.Cells(1, "A").SpecialCells(xlLastCell).Row
で最も使用しているであろう列でBreak。

ではいかが？

お礼

そうですね。
ありがとうございます。