Best way to skip 1st & last cell in range in UDF?

JenniferMurphy

Well-known Member
I have a little UDF that does some tallies on a column of cells (B2:B5).

 R/C A B 1 Group Rating 2 North 25.2 3 South 14.7 4 East 52.0 5 West 33.8 6 Average 31.4

<tbody>
</tbody>

I call the UDF from another cell like this:
Code:
``=Tally(B2:B5)``
I want to change that to include the cell above the first data cell (B1) and the cell after the last data cell (B6) so if I add a row, I don't have to edit the UDF call.
Code:
``=Tally(B1:B6)``
But I want the UDF to skip over these boundary cells. Here's my code, which works.
Code:
``````Public Function Tally(Ratings As Range) As String

Dim cell As Range
Dim i As Integer

i = 0
For Each cell In Ratings
i = i + 1
If (i = 1) Or (i = Ratings.Count) Then
GoTo Continue
End If
. . . do the tallies . . .
Continue:
Next cell

. . . complete the calculations and return the results . . .

End Function``````
Is there a better way to do this?

Thanks

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Joe4

You left out the most important part to us, what is the UDF actually doing?
What are these "tallies"?
Was is it returning?
Is it summing? Averaging?

Fluff

MrExcel MVP, Moderator
Rich (BB code):
``````Public Function Tally(Ratings As Range) As String

Dim i As Long

For i = 2 To Ratings.Rows.Count - 1
'     . . . do the tallies . . .
Next i

'. . . complete the calculations and return the results . . .

End Function``````

JenniferMurphy

Well-known Member
You left out the most important part to us, what is the UDF actually doing?
What are these "tallies"?
Was is it returning?
Is it summing? Averaging?
It's doing all of that and more. But what difference does that make? I want the UDF to step through each of the cells in the range skipping the first one and the last one?

Joe4

I am just trying to figure out if there is a way to avoid the loop altogether (i.e. if we could use something like the AVERAGE or SUM) function.
But of course, we do not know if we can avoid the loop until we understand what kind of calculations are being done in the loop.

Loops are very inefficient, so if you are truly concerned about "speeding things up" or "being more efficient", you want to avoid loops, if at all possible.

Last edited:

JenniferMurphy

Well-known Member
Rich (BB code):
``````Public Function Tally(Ratings As Range) As String

Dim i As Long

For i = 2 To Ratings.Rows.Count - 1
'     . . . do the tallies . . .
Next i

'. . . complete the calculations and return the results . . .

End Function``````
Perfect. And I can get the value in each cell as text using:
Code:
``Ratings(i).Text``
Thank you

JenniferMurphy

Well-known Member
I am just trying to figure out if there is a way to avoid the loop altogether (i.e. if we could use something like the AVERAGE or SUM) function.
But of course, we do not know if we can avoid the loop until we understand what kind of calculations are being done in the loop.
The processing is nothing simple like that.

Loops are very inefficient,
Perhaps, but often necessary.

so if you are truly concerned about "speeding things up" or "being more efficient", you want to avoid loops, if at all possible.
I never said anything about either of those objectives.

Fluff

MrExcel MVP, Moderator
Perfect. And I can get the value in each cell as text using:

Thank you
You're welcome & thanks for the feedback

Joe4

That is true, they are sometimes necessary. But we have no way of knowing if they are or aren't, because you didn't post the details of the code, and what that may be doing.

I never said anything about either of those objectives.
You DID say:
Is there a better way to do this?
Quite frankly, your original code was fine, and did what you need. To be sure, Fluff's code is more concise, and eliminates some uneecessary steps. But while you may save a few lines of code, it probably won't make a huge difference in overall performance. However, if you were able to eliminate loops, that could make a big difference in overall performance. Now maybe you aren't able to, but the question bears asking.

Sometimes people get so focused on little details that in the end may not make much of a difference, while missing bigger things that can make a big difference.