Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
Hi - I have 2 questions: 1. I have dimmed and set a range like TestRange =("B:B,D: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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
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)? -- Rick (MVP - Excel) "jay dean" wrote in message ... Hi - I have 2 questions: 1. I have dimmed and set a range like TestRange =("B:B,D: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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
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: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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
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: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 (MVP - Excel) "jay dean" wrote in message ... 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: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 *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
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)) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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: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 (MVP - Excel) "jay dean" wrote in message ... 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: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 *** |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
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 *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
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 -- Rick (MVP - Excel) "jay dean" wrote in message ... 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 *** |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
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: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 wrote: Hi - I have 2 questions: 1. I have dimmed and set a range like TestRange =("B:B,D: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 *** |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
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 *** |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
RANGES AND DYNAMIC ARRAYS!!
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 jay dean wrote: 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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with ranges in arrays... or an introduction to arrays | Excel Programming | |||
Dynamic Arrays | Excel Discussion (Misc queries) | |||
Dynamic arrays | New Users to Excel | |||
Dynamic Arrays | Excel Worksheet Functions | |||
Lots of Frustration - Lots of Arrays, Dynamic Ranges Don't Work, Help With Options | Excel Programming |