Array

【VBA】シートにあるデータを配列に格納する（空白含まない）｜CurrentRegion

作成日：2022-12-04

更新日：2025-09-30

空白行・空白列で囲まれた内側の範囲を取得する

空白を含まないデータになる

Public Function GetSheetDataCurrent( _
                                            ByVal ws As Worksheet, _
                                            ByVal row As Long, _
                                            ByVal col As Long) As Variant
    GetSheetDataCurrent = ws.Cells(row, col).CurrentRegion.value
End Function

堅牢な作りにする場合

' Get values of the CurrentRegion for a given cell
' 指定セルを基点としたCurrentRegionの値を取得する
Public Function GetCurrentRegionValues( _
	ByVal ws As Worksheet, _
	ByVal row As Long, _
	ByVal col As Long) As Variant

	Dim targetCell As Range
	Dim result As Variant
	
	' Validate input arguments / 入力検証
	If row < 1 Or col < 1 Then
		Exit Function
	End If
	
	' Assign target cell safely / 対象セルの安全な参照
	Set targetCell = ws.Cells(row, col)
	
	' If targetCell is empty, exit / 空セルなら終了
	If IsEmpty(targetCell.Value) Then
		Exit Function
	End If
	
	' Get CurrentRegion values / CurrentRegionを配列で取得
	On Error GoTo ErrHandler
	result = targetCell.CurrentRegion.Value
	GetCurrentRegionValues = result
	On Error GoTo 0
	Exit Function
	
ErrHandler:
	' Optional: return Empty or raise error
	GetCurrentRegionValues = Empty
	On Error GoTo 0
End Function