Excel VBA でデータが入力されている最終行を取得する際、数式が入力されているセルを無視したい場合があります。通常の方法では数式を含むセルも最終行として認識されてしまうため、特別な工夫が必要です。
数式を無視して最終行を取得する方法について、コード例を交えながら解説します。
通常の最終行取得方法
最終行を取得する一般的な方法は、UsedRange や Cells(Rows.Count, Column).End(xlUp) を使用する方法です。
Sub GetLastRow()
Dim lastRow As Long
' A列の最終行を取得
lastRow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox "最終行: " & lastRow
End Sub
問題点
- 上記のコードでは、数式が入力されているセルも「最終行」として認識されます。
- 実際にデータが入力されているセルの最終行だけを取得したい場合には適していません。
数式を無視して最終行を取得する方法
数式を無視するには、HasFormula プロパティを使用してセルに数式が含まれているかどうかを確認し、実際にデータが入力されているセルのみをチェックします。
Sub GetLastRowIgnoringFormulas()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = 0
' A列を下から上にループ
For i = ws.Rows.Count To 1 Step -1
If Not IsEmpty(ws.Cells(i, 1).Value) And Not ws.Cells(i, 1).HasFormula Then
lastRow = i
Exit For
End If
Next i
MsgBox "数式を無視した最終行: " & lastRow
End Sub
解説
IsEmptyを使用して、セルが空白かどうかを判定。HasFormulaプロパティを使用して、セルが数式かどうかを判定。- 両方の条件を満たすセルを「データのみが入力された最終行」として取得。
特定の列に絞って最終行を取得する
特定の列（例: B列）のデータを対象に、数式を無視して最終行を取得する方法です。
Sub GetLastRowInColumn()
Dim ws As Worksheet
Dim lastRow As Long
Dim col As Integer
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
col = 2 ' B列
lastRow = 0
For i = ws.Rows.Count To 1 Step -1
If Not IsEmpty(ws.Cells(i, col).Value) And Not ws.Cells(i, col).HasFormula Then
lastRow = i
Exit For
End If
Next i
MsgBox "B列の数式を無視した最終行: " & lastRow
End Sub
ワークシート全体の最終行を取得する
複数列にデータがある場合、すべての列をチェックして最終行を取得する方法です。
Sub GetLastRowInSheetIgnoringFormulas()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long, j As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = 0
For i = ws.Rows.Count To 1 Step -1
For j = 1 To ws.Columns.Count
If Not IsEmpty(ws.Cells(i, j).Value) And Not ws.Cells(i, j).HasFormula Then
lastRow = i
Exit For
End If
Next j
If lastRow > 0 Then Exit For
Next i
MsgBox "ワークシート全体の数式を無視した最終行: " & lastRow
End Sub
解説
- 外側のループで行（i）を下から上にチェック。
- 内側のループで列（j）を左から右にチェック。
- 最初にデータが見つかった時点で処理を終了します。
使用されている範囲を対象に効率化する
UsedRange を活用して、チェックする範囲を限定することで効率的に最終行を取得できます。
Sub GetLastRowUsingUsedRange()
Dim ws As Worksheet
Dim lastRow As Long
Dim cell As Range
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = 0
For Each cell In ws.UsedRange.Columns(1).Cells
If Not IsEmpty(cell.Value) And Not cell.HasFormula Then
If cell.Row > lastRow Then
lastRow = cell.Row
End If
End If
Next cell
MsgBox "数式を無視した最終行: " & lastRow
End Sub
解説
UsedRangeを使用して、データが入力されている範囲に限定してチェックを行います。- 範囲が限定されるため、大量のデータを扱う場合でも高速に処理できます。
条件付きの最終行取得
特定の条件（例: 値が「100」以上）を満たす最終行を取得する方法も応用できます。
Sub GetLastRowWithCondition()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ThisWorkbook.Worksheets("Sheet1")
lastRow = 0
For i = ws.Rows.Count To 1 Step -1
If Not IsEmpty(ws.Cells(i, 1).Value) And _
Not ws.Cells(i, 1).HasFormula And _
ws.Cells(i, 1).Value >= 100 Then
lastRow = i
Exit For
End If
Next i
MsgBox "条件付きの最終行: " & lastRow
End Sub
注意点
セルのデータ型に注意
数式を含むセルを判定する際、文字列や数値の混在に注意してください。処理速度の最適化
範囲が広い場合、UsedRangeを活用して処理範囲を限定すると高速化できます。空白セルの処理
途中に空白セルがある場合、それを無視するロジックを追加する必要があります。
まとめ
Excel VBA で数式を無視して最終行を取得するには、HasFormula プロパティや IsEmpty を組み合わせたロジックが有効です。主なポイントは以下の通りです。
HasFormulaを使用して数式を判定UsedRangeや特定の列で範囲を限定- 条件付きで最終行を取得する応用