|
こんなので、善いかも?
Option Explicit
Public Sub Sample()
Dim i As Long
Dim lngRows As Long
Dim rngList As Range
Dim vntData As Variant
Dim strResult() As String
Dim strProm As String
'Listの左上隅セル位置を基準として設定(列見出しの最左セル位置)
Set rngList = ActiveSheet.Cells(16, "A")
With rngList
'データ行数を取得
lngRows = .Offset(65536 - .Row).End(xlUp).Row - .Row + 1
'データが無い場合
If lngRows <= 1 And .Value = "" Then
strProm = "データが有りません"
GoTo Wayout
End If
'データを配列に取得
vntData = .Resize(lngRows + 1).Value
End With
'String型の結果用配列を確保
ReDim strResult(1 To lngRows, 1 To 1)
'データを文字列にして結果用配列に格納
For i = 1 To lngRows
strResult(i, 1) = CStr(vntData(i, 1))
Next i
'結果用配列を出力
rngList.Resize(lngRows).Value = strResult
strProm = "処理が完了しました"
Wayout:
Set rngList = Nothing
MsgBox strProm, vbInformation
End Sub
|
|