excel-vba
ワークシートの最後に使用された行または列を検索する方法
サーチ…
備考
他のメソッドがなぜ落胆されているのか/不正確なのかについては、ここで説明しています: http : //stackoverflow.com/a/11169920/4628637
列内の最後の空でないセルを検索する
この例では、データセットの列の最後の空でない行を返すメソッドを見ていきます。
このメソッドは、データセット内の空の領域に関係なく機能します。
End
メソッドがマージされた領域に対して「停止」され、マージされた領域の最初のセルが返されるため、 マージされたセルが含まれる場合は注意が必要です。
さらに、非表示の行の空でないセルは考慮されません。
Sub FindingLastRow()
Dim wS As Worksheet, LastRow As Long
Set wS = ThisWorkbook.Worksheets("Sheet1")
'Here we look in Column A
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
Debug.Print LastRow
End Sub
上記の制限に対処するには、次の行を使用します。
LastRow = wS.Cells(wS.Rows.Count, "A").End(xlUp).Row
次のものに置き換えることができます:
"Sheet1"
最後に使用された行の場合:
LastRow = wS.UsedRange.Row - 1 + wS.UsedRange.Rows.Count
。"Sheet1"
の列"A"
最後の空でないセルの場合:Dim i As Long For i = LastRow To 1 Step -1 If Not (IsEmpty(Cells(i, 1))) Then Exit For Next i LastRow = i
名前付き範囲を使用して最後の行を検索する
シートに名前付き範囲があり、その動的名前付き範囲の最後の行を動的に取得したい場合また、名前付き範囲が最初の行から開始しない場合も対象となります。
Sub FindingLastRow()
Dim sht As Worksheet
Dim LastRow As Long
Dim FirstRow As Long
Set sht = ThisWorkbook.Worksheets("form")
'Using Named Range "MyNameRange"
FirstRow = sht.Range("MyNameRange").Row
' in case "MyNameRange" doesn't start at Row 1
LastRow = sht.Range("MyNameRange").Rows.count + FirstRow - 1
End Sub
更新:
予想外の結果を生成するため、不連続な行を持つ名前付き範囲の@Jeepedによって潜在的な抜け穴が指摘されました。その問題に対処するために、コードは以下のように改訂されています。
Asumptions:targes sheet = form
、名前付き範囲= MyNameRange
Sub FindingLastRow()
Dim rw As Range, rwMax As Long
For Each rw In Sheets("form").Range("MyNameRange").Rows
If rw.Row > rwMax Then rwMax = rw.Row
Next
MsgBox "Last row of 'MyNameRange' under Sheets 'form': " & rwMax
End Sub
範囲内の最後のセルの行を取得する
'if only one area (not multiple areas):
With Range("A3:D20")
Debug.Print .Cells(.Cells.CountLarge).Row
Debug.Print .Item(.Cells.CountLarge).Row 'using .item is also possible
End With 'Debug prints: 20
'with multiple areas (also works if only one area):
Dim rngArea As Range, LastRow As Long
With Range("A3:D20, E5:I50, H20:R35")
For Each rngArea In .Areas
If rngArea(rngArea.Cells.CountLarge).Row > LastRow Then
LastRow = rngArea(rngArea.Cells.CountLarge).Row
End If
Next
Debug.Print LastRow 'Debug prints: 50
End With
ワークシート内の最後の空でない列を検索する
Private Sub Get_Last_Used_Row_Index()
Dim wS As Worksheet
Set wS = ThisWorkbook.Sheets("Sheet1")
Debug.Print LastCol_1(wS)
Debug.Print LastCol_0(wS)
End Sub
ワークシートにデータがないかどうかを知りたいかどうかに関して、2つの選択肢から選択できます。
- NO:LastCol_1を使用:
wS.Cells(...,LastCol_1(wS))
内で直接使用できます - YES:Use LastCol_0:関数から得られた結果が0かどうかをテストしてから使用する必要があります
Public Function LastCol_1(wS As Worksheet) As Double
With wS
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastCol_1 = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
Else
LastCol_1 = 1
End If
End With
End Function
Errオブジェクトのプロパティは、関数が終了すると自動的にゼロにリセットされます。
Public Function LastCol_0(wS As Worksheet) As Double
On Error Resume Next
LastCol_0 = wS.Cells.Find(What:="*", _
After:=ws.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
End Function
Range.CurrentRegionの最後のセル
Range.CurrentRegion
は、空のセルで囲まれた長方形の範囲です。 =""
や'
=""
などの式を持つ空白のセルは空白と見なされません( ISBLANK
Excel関数によっても)。
Dim rng As Range, lastCell As Range
Set rng = Range("C3").CurrentRegion ' or Set rng = Sheet1.UsedRange.CurrentRegion
Set lastCell = rng(rng.Rows.Count, rng.Columns.Count)
ワークシート内の最後の空でない行を検索する
Private Sub Get_Last_Used_Row_Index()
Dim wS As Worksheet
Set wS = ThisWorkbook.Sheets("Sheet1")
Debug.Print LastRow_1(wS)
Debug.Print LastRow_0(wS)
End Sub
ワークシートにデータがないかどうかを知りたいかどうかに関して、2つの選択肢から選択できます。
- NO:LastRow_1を使用:
wS.Cells(LastRow_1(wS),...)
内で直接使用できますwS.Cells(LastRow_1(wS),...)
- YES:Use LastRow_0:関数から得られた結果が0かどうかをテストしてから使用する必要があります
Public Function LastRow_1(wS As Worksheet) As Double
With wS
If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
LastRow_1 = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
Else
LastRow_1 = 1
End If
End With
End Function
Public Function LastRow_0(wS As Worksheet) As Double
On Error Resume Next
LastRow_0 = wS.Cells.Find(What:="*", _
After:=ws.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
End Function
行の最後の空でないセルを検索する
この例では、行の最後の空でない列を返すメソッドを見ていきます。
このメソッドは、データセット内の空の領域に関係なく機能します。
End
メソッドがマージされた領域に対して「停止」され、マージされた領域の最初のセルが返されるため、 マージされたセルが含まれる場合は注意が必要です。
さらに、非表示の列にある空でないセルは考慮されません。
Sub FindingLastCol()
Dim wS As Worksheet, LastCol As Long
Set wS = ThisWorkbook.Worksheets("Sheet1")
'Here we look in Row 1
LastCol = wS.Cells(1, wS.Columns.Count).End(xlToLeft).Column
Debug.Print LastCol
End Sub
ワークシート内の最後の空でないセルを見つける - パフォーマンス(配列)
- 配列を使用する最初の関数ははるかに高速です
- optionalパラメータなしで呼び出された場合、デフォルトは
.ThisWorkbook.ActiveSheet
になります.ThisWorkbook.ActiveSheet
- 範囲が空の場合、
Nothing
ではなくCell( 1, 1 )
がデフォルトとして返されます
速度:
GetMaxCell (Array): Duration: 0.0000790063 seconds
GetMaxCell (Find ): Duration: 0.0002903480 seconds
。MicroTimerで計測
Public Function GetLastCell(Optional ByVal ws As Worksheet = Nothing) As Range
Dim uRng As Range, uArr As Variant, r As Long, c As Long
Dim ubR As Long, ubC As Long, lRow As Long
If ws Is Nothing Then Set ws = Application.ThisWorkbook.ActiveSheet
Set uRng = ws.UsedRange
uArr = uRng
If IsEmpty(uArr) Then
Set GetLastCell = ws.Cells(1, 1): Exit Function
End If
If Not IsArray(uArr) Then
Set GetLastCell = ws.Cells(uRng.Row, uRng.Column): Exit Function
End If
ubR = UBound(uArr, 1): ubC = UBound(uArr, 2)
For r = ubR To 1 Step -1 '----------------------------------------------- last row
For c = ubC To 1 Step -1
If Not IsError(uArr(r, c)) Then
If Len(Trim$(uArr(r, c))) > 0 Then
lRow = r: Exit For
End If
End If
Next
If lRow > 0 Then Exit For
Next
If lRow = 0 Then lRow = ubR
For c = ubC To 1 Step -1 '----------------------------------------------- last col
For r = lRow To 1 Step -1
If Not IsError(uArr(r, c)) Then
If Len(Trim$(uArr(r, c))) > 0 Then
Set GetLastCell = ws.Cells(lRow + uRng.Row - 1, c + uRng.Column - 1)
Exit Function
End If
End If
Next
Next
End Function
'Returns last cell (max row & max col) using Find
Public Function GetMaxCell2(Optional ByRef rng As Range = Nothing) As Range 'Using Find
Const NONEMPTY As String = "*"
Dim lRow As Range, lCol As Range
If rng Is Nothing Then Set rng = Application.ThisWorkbook.ActiveSheet.UsedRange
If WorksheetFunction.CountA(rng) = 0 Then
Set GetMaxCell2 = rng.Parent.Cells(1, 1)
Else
With rng
Set lRow = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
After:=.Cells(1, 1), _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows)
If Not lRow Is Nothing Then
Set lCol = .Cells.Find(What:=NONEMPTY, LookIn:=xlFormulas, _
After:=.Cells(1, 1), _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns)
Set GetMaxCell2 = .Parent.Cells(lRow.Row, lCol.Column)
End If
End With
End If
End Function
。
Private Declare PtrSafe Function getFrequency Lib "Kernel32" Alias "QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare PtrSafe Function getTickCount Lib "Kernel32" Alias "QueryPerformanceCounter" (cyTickCount As Currency) As Long
Function MicroTimer() As Double
Dim cyTicks1 As Currency
Static cyFrequency As Currency
MicroTimer = 0
If cyFrequency = 0 Then getFrequency cyFrequency 'Get frequency
getTickCount cyTicks1 'Get ticks
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency 'Returns Seconds
End Function