Trim Entire Column in VBA

Sub TrimCol()
    Dim r As Range
    Set r = Intersect(Range("A1").EntireColumn, ActiveSheet.UsedRange)
    r.Value = Evaluate("IF(ROW(" & r.Address & "),IF(" & r.Address & "<>"""",TRIM(" & r.Address & "),""""))")
End Sub

Copy Range to Array

Dim myArray()
Sub copyRange2Array()
myArray = ThisWorkbook.Sheets(1).Range("A1:A4").Value
For i = 1 To UBound(myArray)
    MsgBox myArray(i, 1)
Next
End Sub

Finding Next Blank Row no in VBA

Sub test()
Dim myrng As Range
Set myrng = Range(ThisWorkbook.Sheets(1).Range("A1").Offset(0, 0), ThisWorkbook.Sheets(1).Range("A1").Offset(0, 0).End(xlDown))
MsgBox myrng.Rows.Count + 1
End Sub