=ЕСЛИОШИБКА(ИНДЕКС(A$1:A$998;НАИМЕНЬШИЙ(ЕСЛИ(A$2:A$999<>"";СТРОКА(A$2:A$999));СТРОКА(D1)));"")

Option Explicit
Option Base 1

Sub Подтянуть()
'---------------
'For: user Leojse
'Topic: http://www.excelworld.ru/forum/10-12562-1
'Author: tsap
'---------------
Dim h As Long, w As Integer
Dim k As Long, i As Long, j As Long
Dim ar, ar_new() As String

h = Selection.Rows.Count
w = Selection.Columns.Count
ar = Selection.Value

ReDim ar_new(h, w)
For i = 1 To w
     k = 0
     For j = 1 To h
         If Not IsEmpty(ar(j, i)) Then
         k = k + 1
         ar_new(k, i) = ar(j, i)
         End If
     Next
Next
'Заполняем выделенный диапазон и выравниваем по правому краю
Selection = ar_new
Selection.HorizontalAlignment = xlRight
End Sub