こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

投稿日時 - 2005-11-16 11:41:06

QNo.1782196

困ってます

質問者が選んだベストアンサー

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

・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

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

投稿日時 - 2005-11-16 12:29:03

お礼

なるほど。

ありがとうございます。

投稿日時 - 2005-11-16 15:09:55

ANo.1

このQ&Aは役に立ちましたか?

16人が「このQ&Aが役に立った」と投票しています

回答(11)

ANo.11

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

投稿日時 - 2005-11-17 11:52:57

お礼

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

投稿日時 - 2005-11-17 13:14:25

ANo.10

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

#9 で、、

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

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

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

投稿日時 - 2005-11-16 17:42:04

ANo.9

#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

投稿日時 - 2005-11-16 16:15:49

お礼

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

投稿日時 - 2005-11-17 10:02:26

ANo.8

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

投稿日時 - 2005-11-16 16:02:21

お礼

ありがとうございます。

投稿日時 - 2005-11-17 10:01:47

ANo.7

私の案はこんな感じ。

何と言ってもLastCellを使わない手は無いと思う。
その1:
 一旦上書き保存してからLastCellを取得する。
その2:
 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

投稿日時 - 2005-11-16 15:45:51

お礼

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

投稿日時 - 2005-11-17 10:00:45

ANo.6

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

投稿日時 - 2005-11-16 15:28:51

ANo.5

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

投稿日時 - 2005-11-16 15:24:39

お礼

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

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

勉強になりました!

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

投稿日時 - 2005-11-17 09:57:52

ANo.4

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

投稿日時 - 2005-11-16 15:04:27

お礼

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

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

投稿日時 - 2005-11-16 15:17:34

ANo.3

Sub test02()
d = Range("A65536").End(xlUp).Row
r = Range("IV1").End(xlToLeft).Column
MsgBox d
MsgBox r
End Sub
でやれば途中空白行があっても最下行(行番号)、最右列を取れますが。
UsedRange、CurrentRegionもそれぞれ特徴は
ありますが。
何が困っているのかよくわからないですがとりあえず。

投稿日時 - 2005-11-16 13:12:01

お礼

ありがとうございます。

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

投稿日時 - 2005-11-16 15:07:57

ANo.2

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

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

ではいかが?

投稿日時 - 2005-11-16 12:31:56

お礼

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

投稿日時 - 2005-11-16 15:10:57

あなたにオススメの質問