RANGES AND DYNAMIC ARRAYS!!

[jay dean]

Hi - I have 2 questions:

1. I have dimmed and set a range like TestRange =("B:B,D,F:F,H:H").
How do I programmatically determine the last cell in the range that
contains data (as well as the address of this cell)?


2. In the same range above, I need a macro to load the data into a
dynamic array, say DynArray. The data in the range will all be integers.


Any help will be appreciated.
Thanks
Jay

*** Sent via Developersdex http://www.developersdex.com ***

 

[Rick Rothstein]

We (or at least I) need some clarification...

1. Which is the "last cell"... the one in the highest row number or the one
in the right-most column (we don't know which direction your data is laid
out in)?

2. What type of array are you looking to end up with... a one dimensional
array (with the data snaked column-to-column or row-to-row) or a two
dimensional array (which dimension is the row and which is the column)?

 

[jay dean]

Hi Rick - Here is the clarification:

(1) The amount of data in the range is not constant. Therefore, the
"last cell" filled could be in the "right-most" column, or the column
next to the "right-most" or the column 2nd next to the "right-most"
column, and so on. The direction of the data is this way: "Fill the
"left-most" column B:B (from top-down), then continue in the next
"left-most" column D (from the top-down),then continue in the next
"left-most" column F:F (from the top-down), and so on..

(2) The type of array I am looking to end up with is either a (1 row by
N columns) OR (N rows by 1 column)., where N is the number of cells
containing data in the "snake-like" range structure.. (that is, starting
from B1 to B65536, then continuing from D1 (acting as the 65537th cell)
through D65536 (acting as cell 2*65536), then continuing in F1 through
F65536, and so on.

I hope this clears it up.
Thanks
Jay

*** Sent via Developersdex http://www.developersdex.com ***

 

[Rick Rothstein]

I'm answering this during a short "break" time with the company I have over
this morning, so this is not a complete answer yet (they are leaving soon,
so I'll get to the second part shortly). This is my response to the first
part...

Dim LastCell As Range
Dim LastCellValue As Variant
Dim LastCellAddress As String
Set LastCell = Range("B:B,D,F:F,H:H").Find(What:="*", _
SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
LastCellAddress = LastCell.Address(0, 0)
LastCellValue = LastCell.Value
'
' Show that it worked
'
MsgBox "Address = " & LastCellAddress & vbLf & "Value = " & LastCellValue

 

[Rick Rothstein]

Okay, for your second question, this code will create a one-dimensional
String array (which I named SnakedArray in my code) containing your data in
snaked order (all of Column B's data followed by all of Column D's data,
etc.)...

Dim R As Range
Dim X As Long
Dim LastRow As Long
Dim ColData As String
Dim ColumnArray() As String
Dim SnakedArray() As String
Const StartRow As Long = 1
Const Cols As String = "B D F H"
ColumnArray = Split(Cols)
For X = 0 To 3
Set R = Range(ColumnArray(X) & StartRow & ":" & ColumnArray(X) & _
Cells(Rows.Count, ColumnArray(X)).End(xlUp).Row)
If X Then ColData = ColData & Chr(1)
If R.Count = 1 Then
If R.Value <> "" Then
ColData = ColData & R.Value
Else
ColData = Left(ColData, Len(ColData) - 1)
End If
Else
ColData = ColData & Join(WorksheetFunction.Transpose(R), Chr(1))
End If
Next
SnakedArray = Split(ColData, Chr(1))

 

[jay dean]

Rick, your first solution works fine.. However, I am not sure about your
construction of the SnakedArray. For example, I filled the columns with
lots of data, and when I did MsgBox (UBound(SnakedArray)), it gave me 3
which is not correct.

If the SnakedArray works fine, then I expect that if for example, A:A is
filled completely from to to bottom and B:B is filled from B1 to B100,
then the length of the SnakedArray after loading the data in the range
ought to be (65536+100)=65636.. Therefore, Ubound(SnakedArray) should
give me 65635 (assuming indexing starts from 0)

Thanks
Jay


*** Sent via Developersdex http://www.developersdex.com ***

 

[Rick Rothstein]

Okay, how about this code instead...

Sub FillSnakedArray()
Dim R As Range
Dim X As Long
Dim LastRow As Long
Dim ColData As String
Dim ColumnArray() As String
Dim SnakedArray() As String
Const StartRow As Long = 1
Const Cols As String = "B D F H"
ColumnArray = Split(Cols)
On Error GoTo Done
For X = 0 To 3
Set R = Range(ColumnArray(X) & StartRow & ":" & ColumnArray(X) & _
Range(ColumnArray(X) & ":" & ColumnArray(X)). _
Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row)
If X Then ColData = ColData & Chr(1)
If R.Count = 1 Then
If R.Value <> "" Then
ColData = ColData & R.Value
Else
ColData = Left(ColData, Len(ColData) - 1)
End If
ElseIf R.Count = Rows.Count Then
ColData = ColData & Join(WorksheetFunction.Transpose( _
R.Resize(Rows.Count - 1)), Chr(1)) & Chr(1) & _
Cells(Rows.Count, ColumnArray(X)).Value
Else
ColData = ColData & Join(WorksheetFunction.Transpose(R), Chr(1))
End If
Next
Done:
SnakedArray = Split(ColData, Chr(1))
End Sub

 

[keiji kounoike]

About 2. try this one, although this one is rather straightforward than
Rick's technical one. I assume all of your data is Constant values, not
including formulas. if this is not case, delete every ' in the code. but
be careful, this will delete all of your formulas.

Sub JointCol2Array()
Dim Uniarray()
Dim i As Long
Dim col As Range, tmp As Range, rng As Range

Set testrange = Range("B:B,D,F:F,H:H")

Application.ScreenUpdating = False
i = 0
'if all of your data is not constant, comment out For Loop below.
'For Each col In testrange.Columns
' col.Copy
' col.PasteSpecial xlPasteValues
'Next

Set tmp = testrange.SpecialCells(xlCellTypeConstants)
ReDim Preserve Uniarray(tmp.Cells.Count - 1)

For Each rng In tmp
Uniarray(i) = rng.Value
i = i + 1
Next
Application.CutCopyMode = False
tmp.Select
End Sub

Keiji

 

[jay dean]

Thanks, Keiji! This might not be as 'technical' as Rick's approach, but
it does the job!

Thanks again!
Jay



*** Sent via Developersdex http://www.developersdex.com ***

 

[keiji kounoike]

Thanks for the feedback. I make it a rule to write a code as easy as
possible to read and easy to modify for future changes. if the time of
process is slow enough to be unbearable, then try to think about
speeding it up, though it might not be always possible.

Keiji