Last active
April 22, 2020 01:12
-
-
Save furyutei/4933bec11ac7c1ac084e26cc4b0c1ea0 to your computer and use it in GitHub Desktop.
ExcelでWorksheetの最終行を取得する
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sub 最終行取得テスト() | |
Debug.Print "引数省略時(CheckCol:=1)→" & getLastRow() | |
Debug.Print "(CheckCol:=0)→" & getLastRow(CheckCol:=0) | |
End Sub | |
' getLastRow(): 最終行取得関数 | |
Private Function getLastRow(Optional WS As Worksheet = Nothing, Optional CheckCol As Long = 1) As Long | |
' WS: 対象Worksheet | |
' CheckCol: 対象列番号 ※ 0(以下)の場合、全列対象 | |
If WS Is Nothing Then Set WS = ActiveSheet | |
getLastRow = 0 | |
If 0 < CheckCol Then | |
Set UR = Intersect(WS.UsedRange, WS.Columns(CheckCol)) | |
Else | |
Set UR = WS.UsedRange | |
End If | |
If UR Is Nothing Then Exit Function | |
Set lastCell = UR.Find("*", LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious) | |
If Not WS.AutoFilterMode Then | |
'AutoFilter無効時にはシンプルに最終行が決まる | |
If Not lastCell Is Nothing Then getLastRow = lastCell.Row | |
Exit Function | |
End If | |
'AutoFilter有効時には、Find()では非表示セルが対象にならない | |
'チェック範囲の絞り込み(表示セル中でデータ有りの最終行以降を対象) | |
If lastCell Is Nothing Then | |
firstRow = 1 | |
Else | |
firstRow = lastCell.Row | |
End If | |
lastRow = UR.Rows(UR.Rows.Count).Row | |
Set UR = WS.Range(WS.Cells(firstRow, UR.Column), WS.Cells(lastRow, UR.Columns(UR.Columns.Count).Column)) | |
With UR | |
range_values = .Value | |
If Not IsArray(range_values) Then | |
' チェック対象がただひとつのセルの場合は配列にならない | |
If Not IsEmpty(range_values) Then getLastRow = .Rows(.Rows.Count).Row | |
Exit Function | |
End If | |
For range_row = .Rows.Count To 1 Step -1 | |
For range_col = LBound(range_values, 2) To UBound(range_values, 2) | |
If Not IsEmpty(range_values(range_row, range_col)) Then | |
getLastRow = .Rows(range_row).Row | |
Exit Function | |
End If | |
Next range_col | |
Next range_row | |
End With | |
End Function |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
お題は
より。