JenniferMurphy
Well-known Member
- Joined
- Jul 23, 2011
- Messages
- 2,535
- Office Version
- 365
- Platform
- Windows
I am working on a couple of UDFs that process a list of values in sheet cells. The problem is that the list could be in a row (D5:G5) or a column (D5:D25).
If I process the cells one at a time from the sheet, I have to know whether to increment the row or column numbers. If I read the entire range into an array, I get either a 1xN or Nx1 array so I need to know whether to increment the first or second dimension.
Question #1: If I process the cells one at a time from the sheet, I am using code like this which loops through both the rows and columns, one of which will be a 1 iteration loop.
Is there a better way to do this?
Question #2: If I read the range into an array, I can use similar code to loop thru both dimensions, but
Question #3: Is it more efficient to read the entire range into an array and process it there (second question above) vs reading the cells one at a time from the sheet (first question above)?
Thanks for any help
If I process the cells one at a time from the sheet, I have to know whether to increment the row or column numbers. If I read the entire range into an array, I get either a 1xN or Nx1 array so I need to know whether to increment the first or second dimension.
Question #1: If I process the cells one at a time from the sheet, I am using code like this which loops through both the rows and columns, one of which will be a 1 iteration loop.
VBA Code:
NumRows = Range(RangeName).Rows.Count
NumCols = Range(RangeName).Columns.Count
For i = 1 to NumRows
For j = 1 to NumCols
Data = Range(RangeName).Cells(i, j).Value
Next J
Next i
Question #2: If I read the range into an array, I can use similar code to loop thru both dimensions, but
- Is there a way I can read a range that is either a single ow or a single column into a one dimensional array.
- Is there a way I can convert a 1xN array or an Nx1 array into a one dimensional array?
- Is there a way I can "transpose" a 1xN array into an Nx1 array?
Question #3: Is it more efficient to read the entire range into an array and process it there (second question above) vs reading the cells one at a time from the sheet (first question above)?
Thanks for any help