Sum of Len(string) for an entire row

[durex]

Having a hard time wrapping my head around this one as Im still rusty on the actual VB functions specific to excel...

Im looking for vb code that will return the sum of all characters in a particular row, where the column header row (row 1) is not empty...

Thanks in advance!

[Leith Ross]

Hello Durex,

Here is a Worksheet Formula Example to sum everything in row 2...

=SUM("2:2")

Example of summing multiple rows. Rows 2 through 10...

=SUM("2:10")

Example of summing a Range...

=SUM("A2:R240")


Hope this helps,
Leith Ross

[durex]

Actually, to do what Im looking to do using an excel formula, you need to use an array formula..

i.e.
For Summing all characters for row 2, columns A2 through G2...
{=SUM(LEN(A2:G2))}

But, Im not looking for the excel formula... Im looking how to do this in vbcode.

Thanks for the suggestion!

[Leith Ross]

Hello Durex,

I played it safe answering your first question. Sometimes people say VB and worksheet formula and vice versa. So, here's what to do.

Please Login or Register  to view this content.

To use it:

Dim X

X = AddRow("A1:G1")

X = Sum of the row. At least the sum of any valid numbers in the row.


Sincerely,
Leith Ross

[Gary Keramidas]

try this
Range("h2").FormulaArray = "=sum(len(A2:g2))"

--


Gary


"durex" <[email protected]> wrote in
message news:[email protected]...
>
> Actually, to do what Im looking to do using an excel formula, you need
> to use an array formula..
>
> i.e.
> For Summing all characters for row 2, columns A2 through G2...
> {=SUM(LEN(A2:G2))}
>
> But, Im not looking for the excel formula... Im looking how to do this
> in vbcode.
>
> Thanks for the suggestion!
>
>
> --
> durex
> ------------------------------------------------------------------------
> durex's Profile:
> http://www.excelforum.com/member.php...o&userid=27857
> View this thread: http://www.excelforum.com/showthread...hreadid=478407
>

[Gary Keramidas]

and if you need the result in a variable, this should do it

TotalLen = Application.Evaluate("=sum(len(a2:g2))")

--


Gary


"durex" <[email protected]> wrote in
message news:[email protected]...
>
> Actually, to do what Im looking to do using an excel formula, you need
> to use an array formula..
>
> i.e.
> For Summing all characters for row 2, columns A2 through G2...
> {=SUM(LEN(A2:G2))}
>
> But, Im not looking for the excel formula... Im looking how to do this
> in vbcode.
>
> Thanks for the suggestion!
>
>
> --
> durex
> ------------------------------------------------------------------------
> durex's Profile:
> http://www.excelforum.com/member.php...o&userid=27857
> View this thread: http://www.excelforum.com/showthread...hreadid=478407
>

[Ron Rosenfeld]

On Fri, 21 Oct 2005 18:30:07 -0500, durex
<[email protected]> wrote:

>
>Having a hard time wrapping my head around this one as Im still rusty on
>the actual VB functions specific to excel...
>
>Im looking for vb code that will return the sum of all characters in a
>particular row, where the column header row (row 1) is not empty...
>
>Thanks in advance!

One simple way to do this in code is to just step through each cell. So:

===============
Function LenRow(rg As Range) As Double
Dim c As Range
Dim i As Long

For Each c In rg
LenRow = LenRow + Len(c.Text)
Next c
End Function
================


--ron

[durex]

Excellent! Thanks so much! I was actually looking for the total length of all strings in a particular row, but that got me in the direction I needed! Just made a minor modification and it worked like a charm.

Please Login or Register  to view this content.

Just Changed the "Val" to "Len"

Thanks again for the super fast reply!