Hi NG.

I have a spreadsheet with 7 columns and 150000 rows.

For each column i want to know max. character include spaces.

I can test all cells, one by one per column, but I need at faster way.

Any suggestion?

--
Best regards
Jorgen Bondesen

On Mon, 27 Oct 2008 21:17:56 +0100, "Joergen Bondesen"
wrote:

Hi NG.

I have a spreadsheet with 7 columns and 150000 rows.

For each column i want to know max. character include spaces.

I can test all cells, one by one per column, but I need at faster way.

Any suggestion?

Try the following formula to get the max number of characters in
column A from line 1 to 150000

=MAX(LEN(A1:A150000))

Note: The formula must be entered as an array formula, i.e. with
CRTL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

Use an array formula at the bottom of each column. For example if you want
to see the longest strings in cells A1 to A150000 use the formula:-

=MAX(LEN(A1:A150000))

Don't just press enter after entering this formula, hold down Crtl and Shift
and then press enter.

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Joergen Bondesen" wrote:

Hi NG.

I have a spreadsheet with 7 columns and 150000 rows.

For each column i want to know max. character include spaces.

I can test all cells, one by one per column, but I need at faster way.

Any suggestion?

--
Best regards
Jorgen Bondesen

Hi Lars-Åke

Thanks, it works, but I need a VBA solution. My mistake, sorry.


--
Best regards
Jorgen Bondesen


"Lars-Åke Aspelin" skrev i en meddelelse
...
On Mon, 27 Oct 2008 21:17:56 +0100, "Joergen Bondesen"
wrote:

Hi NG.

I have a spreadsheet with 7 columns and 150000 rows.

For each column i want to know max. character include spaces.

I can test all cells, one by one per column, but I need at faster way.

Any suggestion?

Try the following formula to get the max number of characters in
column A from line 1 to 150000

=MAX(LEN(A1:A150000))

Note: The formula must be entered as an array formula, i.e. with
CRTL+SHIFT+ENTER rather than just ENTER.

Hope this helps / Lars-Åke

Hi Alan

Thanks, it works fine, but I need a VBA solution. My mistake, sorry.

--
Best regards
Jorgen Bondesen


"Alan Moseley" skrev i en meddelelse
...
Use an array formula at the bottom of each column. For example if you
want
to see the longest strings in cells A1 to A150000 use the formula:-

=MAX(LEN(A1:A150000))

Don't just press enter after entering this formula, hold down Crtl and
Shift
and then press enter.

--
Alan Moseley IT Consultancy
http://www.amitc.co.uk

If I have solved your problem, please click Yes below. Thanks.


"Joergen Bondesen" wrote:

Hi NG.

I have a spreadsheet with 7 columns and 150000 rows.

For each column i want to know max. character include spaces.

I can test all cells, one by one per column, but I need at faster way.

Any suggestion?

--
Best regards
Jorgen Bondesen

You can use the Evaluate method to have Excel parse and calculate a
formula passed in as a string. E.g.,

Dim L As Long
' watch for the pairs of " characters.
L = Application.Evaluate("=MAX(LEN(""A1:A100""))")
Debug.Print L

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Mon, 27 Oct 2008 22:24:30 +0100, "Joergen Bondesen"
wrote:

Hi Alan

Thanks, it works fine, but I need a VBA solution. My mistake, sorry.

Just to add to Chip's response...

I think I'd use this:

L = worksheets("Sheet999").Evaluate("=MAX(LEN(""A1:A10 0""))")

Then I wouldn't have to worry about what worksheet was active when the code ran.

Chip Pearson wrote:

You can use the Evaluate method to have Excel parse and calculate a
formula passed in as a string. E.g.,

Dim L As Long
' watch for the pairs of " characters.
L = Application.Evaluate("=MAX(LEN(""A1:A100""))")
Debug.Print L

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 22:24:30 +0100, "Joergen Bondesen"
wrote:

Hi Alan

Thanks, it works fine, but I need a VBA solution. My mistake, sorry.

--

Dave Peterson

Just to add to Chip's response...

I think I'd use this:

Good catch. You're absolutely correct.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 18:33:37 -0500, Dave Peterson
wrote:

Just to add to Chip's response...

I think I'd use this:

L = worksheets("Sheet999").Evaluate("=MAX(LEN(""A1:A10 0""))")

Then I wouldn't have to worry about what worksheet was active when the code ran.

Chip Pearson wrote:

You can use the Evaluate method to have Excel parse and calculate a
formula passed in as a string. E.g.,

Dim L As Long
' watch for the pairs of " characters.
L = Application.Evaluate("=MAX(LEN(""A1:A100""))")
Debug.Print L

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 22:24:30 +0100, "Joergen Bondesen"
wrote:

Hi Alan

Thanks, it works fine, but I need a VBA solution. My mistake, sorry.

Hi Chip and Dave


Thank, but i do not work for me.
L = 7 Why ?

Do you have any suggestion?

I'm using Danish Excel 2007 and Danish Win XP


Info Len
abc 3
abc abc 8
abcdefghij 10
1232 4



Option Explicit

Sub test()
Dim L As Long
L = Worksheets("Test").Evaluate("=MAX(LEN(""A1:A100"") )")

MsgBox L
End Sub

L must be = 10

--
Best regards
Joergen Bondesen


"Chip Pearson" skrev i en meddelelse
...
Just to add to Chip's response...

I think I'd use this:

Good catch. You're absolutely correct.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 18:33:37 -0500, Dave Peterson
wrote:

Just to add to Chip's response...

I think I'd use this:

L = worksheets("Sheet999").Evaluate("=MAX(LEN(""A1:A10 0""))")

Then I wouldn't have to worry about what worksheet was active when the
code ran.

Chip Pearson wrote:

You can use the Evaluate method to have Excel parse and calculate a
formula passed in as a string. E.g.,

Dim L As Long
' watch for the pairs of " characters.
L = Application.Evaluate("=MAX(LEN(""A1:A100""))")
Debug.Print L

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 22:24:30 +0100, "Joergen Bondesen"
wrote:

Hi Alan

Thanks, it works fine, but I need a VBA solution. My mistake, sorry.

Try:
L = Worksheets("Test").Evaluate("MAX(LEN(A1:A100))")

You don't need the leading = sign (but it won't hurt).

And Chip got enthusiastic with those double quotes <vbg (and I didn't notice).

So you were really finding the length of this string:
A1:A100
Which is exactly 7 characters!



Joergen Bondesen wrote:

Hi Chip and Dave

Thank, but i do not work for me.
L = 7 Why ?

Do you have any suggestion?

I'm using Danish Excel 2007 and Danish Win XP

Info Len
abc 3
abc abc 8
abcdefghij 10
1232 4

Option Explicit

Sub test()
Dim L As Long
L = Worksheets("Test").Evaluate("=MAX(LEN(""A1:A100"") )")

MsgBox L
End Sub

L must be = 10

--
Best regards
Joergen Bondesen

"Chip Pearson" skrev i en meddelelse
...
Just to add to Chip's response...

I think I'd use this:

Good catch. You're absolutely correct.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 18:33:37 -0500, Dave Peterson
wrote:

Just to add to Chip's response...

I think I'd use this:

L = worksheets("Sheet999").Evaluate("=MAX(LEN(""A1:A10 0""))")

Then I wouldn't have to worry about what worksheet was active when the
code ran.

Chip Pearson wrote:

You can use the Evaluate method to have Excel parse and calculate a
formula passed in as a string. E.g.,

Dim L As Long
' watch for the pairs of " characters.
L = Application.Evaluate("=MAX(LEN(""A1:A100""))")
Debug.Print L

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 22:24:30 +0100, "Joergen Bondesen"
wrote:

Hi Alan

Thanks, it works fine, but I need a VBA solution. My mistake, sorry.

--

Dave Peterson

Hi Dave

Perfect.
Thanks.

--
Best regards
Joergen Bondesen


"Dave Peterson" skrev i en meddelelse
...
Try:
L = Worksheets("Test").Evaluate("MAX(LEN(A1:A100))")

You don't need the leading = sign (but it won't hurt).

And Chip got enthusiastic with those double quotes <vbg (and I didn't
notice).

So you were really finding the length of this string:
A1:A100
Which is exactly 7 characters!



Joergen Bondesen wrote:

Hi Chip and Dave

Thank, but i do not work for me.
L = 7 Why ?

Do you have any suggestion?

I'm using Danish Excel 2007 and Danish Win XP

Info Len
abc 3
abc abc 8
abcdefghij 10
1232 4

Option Explicit

Sub test()
Dim L As Long
L = Worksheets("Test").Evaluate("=MAX(LEN(""A1:A100"") )")

MsgBox L
End Sub

L must be = 10

--
Best regards
Joergen Bondesen

"Chip Pearson" skrev i en meddelelse
...
Just to add to Chip's response...

I think I'd use this:

Good catch. You're absolutely correct.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 18:33:37 -0500, Dave Peterson
wrote:

Just to add to Chip's response...

I think I'd use this:

L = worksheets("Sheet999").Evaluate("=MAX(LEN(""A1:A10 0""))")

Then I wouldn't have to worry about what worksheet was active when the
code ran.

Chip Pearson wrote:

You can use the Evaluate method to have Excel parse and calculate a
formula passed in as a string. E.g.,

Dim L As Long
' watch for the pairs of " characters.
L = Application.Evaluate("=MAX(LEN(""A1:A100""))")
Debug.Print L

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

On Mon, 27 Oct 2008 22:24:30 +0100, "Joergen Bondesen"
wrote:

Hi Alan

Thanks, it works fine, but I need a VBA solution. My mistake, sorry.

--

Dave Peterson