How to read a row or column range with the same code?

[JenniferMurphy]

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.

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

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

1. Is there a way I can read a range that is either a single ow or a single column into a one dimensional array.
2. Is there a way I can convert a 1xN array or an Nx1 array into a one dimensional array?
3. 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

 

[Anthony47]

Question #1: if you have few cells (<100-500) I shouldn't bother about "the fastest" way

#2-1 You have to declare a one dimensional array and then populate it cell by cell; or populate an Array and the use #2-2
#2-2 You may use Application.WorksheetFunction.Transpose(myArray,xx,0); this will return all the columns of row xx; if you use 0,xx you will get all rows of column xx
#2-3 I guess you now know that you can use Application.WorksheetFunction.Transpose

Bye

 

[dmt32]

Hi,
just something for you to try - have play with following & see if of any help to you

You pass an argument to function (xlRows = 1, or xlColumns = 2) to return the area of the range you want

Function GetRange(ByVal RowCol As XlRowCol) As Variant
    GetRange = Range("D5:G5,D5:D25").Areas(RowCol).Value
End Function


Sub ByRows()
    Dim cell As Variant
    For Each cell In GetRange(xlRows)
    
    MsgBox cell
    
    Next cell
End Sub

Sub ByColumns()
    Dim cell As Variant
    For Each cell In GetRange(xlColumns)
    
    MsgBox cell
    
    Next cell
End Sub

Dave

 

[Anthony47]

#2-2 You may use Application.WorksheetFunction.Transpose(myArray,xx,0); this will return all the columns of row xx; if you use 0,xx you will get all rows of column xx
#2-3 I guess you now know that you can use Application.WorksheetFunction.Transpose

#2-2: NO, not Transpose but Index, example Application.WorksheetFunction.Index(myArray,xx,0)
(This is the Excel INDEX function)
#2-3 To transpose an Array you will use Transpose, example Array1xN=Application.WorksheetFunction.Transpose(ArrayNx1)

Don't miss Dave's message, above

Bye

 

[Marc L]

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

According to this 'non sense' piece of code as it is, just removing the useless, an easy way whatever the source range :​

    For Each Data In Range(RangeName).Value
        ' do your stuff here as the Variant variable Data is already allocated with the For codeline …
    Next

 

[JenniferMurphy]

According to this 'non sense' piece of code as it is, just removing the useless, an easy way whatever the source range :​

    For Each Data In Range(RangeName).Value
        ' do your stuff here as the Variant variable Data is already allocated with the For codeline …
    Next

I have been meaning to learn more about the "For each ..." syntax. I learn coding way back before everything was "structured". I've been slowly getting up to speed with modern methods.

Oh, and thanks for kind words/characterizations.

 

[Marc L]

For question #2 if an one dimension array variable is really necessary (not always the case) :​

        Dim V
    With Range(RangeName)
        If .Columns.Count = 1 Then V = Application.Transpose(.Cells) Else If .Rows.Count = 1 Then V = Application.Index(.Value, 1, 0) _
                                                                     Else Beep: Exit Sub
    End With
        Stop

 

[JenniferMurphy]

According to this 'non sense' piece of code as it is, just removing the useless, an easy way whatever the source range :​

Oh great coding guru and master of benevolence and magnanimity, does this adjusted code largely escape being characterized as "non sense" (sic) and "useless"?

Sub SelectionForEachTest()

Dim Weights As Range
Set Weights = Selection
Dim Weight As Variant
Dim Sum As Double
Sum = 0

For Each Weight In Selection
  Sum = Sum + Weight
Next
 
MsgBox Sum
 
End Sub

 

[Marc L]

​

This one has obviously some sense but should be achieved without any 'useless' loop with a single codeline according to Excel basics !​

​

For question #2 see above post #7 …​