Option Base 1
Public SH As Worksheet


Function InputWorksheet()
Set SH = ThisWorkbook.Sheets("InputData")
End Function

Function LastRow()
LastRow = SH.Range("A" & Rows.Count).End(xlUp).Row
End Function

Sub Create_SingleDimension_Array_Optionbase_1()
'Dim SingleDim(5) As String ----Declare static variable
'www.Tricks12345.com
Dim AllTheData() As Variant
Dim StrData() As String
Dim IntData() As Integer
Strval = 1: intval = 1
InputWorksheet
For ArrayIndex = 1 To LastRow
ReDim Preserve AllTheData(ArrayIndex)
AllTheData(ArrayIndex) = SH.Range("A" & ArrayIndex).Value
'MsgBox UBound(AllTheData)
'MsgBox SH.Range("A" & ArrayIndex).Value
If Application.WorksheetFunction.IsText(SH.Range("A" & ArrayIndex).Value) = True Then
ReDim Preserve StrData(Strval)
StrData(Strval) = SH.Range("A" & ArrayIndex).Value
Strval = Strval + 1
End If
If Application.WorksheetFunction.IsNumber(SH.Range("A" & ArrayIndex).Value) = True Then
ReDim Preserve IntData(intval)
IntData(intval) = SH.Range("A" & ArrayIndex).Value
intval = intval + 1
End If
Next
'MsgBox LBound(AllTheData)
'MsgBox UBound(AllTheData)
For i = LBound(AllTheData) To UBound(AllTheData)
SH.Range("B" & i).Value = AllTheData(i)
Next
For i = LBound(StrData) To UBound(StrData)
SH.Range("C" & i).Value = StrData(i)
Next
For i = LBound(IntData) To UBound(IntData)
SH.Range("D" & i).Value = IntData(i)
Next
End Sub