From: "Tom Ogilvy" <Thomas.Ogilvy2@hqda.army.mil>
References: <rtu7pa4b1iv79@corp.supernews.com> <OTQA6m3$#GA.282@cppssbbsa04>
Subject: Re: Simple Question
Date: Wed, 15 Sep 1999 09:44:23 -0400
Newsgroups: microsoft.public.excel.programming


Floyd.
when picking up a vertical range from a worksheet, to get a single dimension
array, you need to use application.transpose, otherwise it will be a
Array(number of rows, 1 column) two dimensional array

This picks up columns A and B and then Scrunches them down so they only
contain values where A was not blank.

Sub Tester3()
Dim rng As Range
Dim ColumnA As Variant
Dim ColumnB As Variant
Dim i As Long, j As Long
With Worksheets("sheet1")
set rng = .Range(.Cells(1, 1), .Cells(Rows.Count, "A").End(xlUp))
End With
ColumnA = Application.Transpose(rng.Value)
ColumnB = Application.Transpose(rng.Offset(0, 1))
j = UBound(ColumnA)
For i = LBound(ColumnA) To UBound(ColumnA)
  If Len(Trim(CStr(ColumnA(i)))) = 0 Then
     If j > i Then j = i
  Else
     If j < i Then
      ColumnA(j) = ColumnA(i)
      ColumnB(j) = ColumnB(i)
      j = j + 1
     End If
  End If
Next
If j <> UBound(ColumnA) Then
  ReDim Preserve ColumnA(LBound(ColumnA) To j - 1)
  ReDim Preserve ColumnB(LBound(ColumnB) To j - 1)
End If
For i = LBound(ColumnA) To UBound(ColumnA)
  Debug.Print i, ColumnA(i), ColumnB(i)
Next
End Sub

Regards,
Tom Ogilvy