Max data return for lengh, but need max less than 300

Posted on 2011-03-02
Last Modified: 2012-05-11
I have received the answer on how to loop through columns to get the max length for each one.  I have attached all the code and it does work.  However, the first column will always return the 300 + as that is where the footnote is located, so now, how to I actually get the Max value, but the max value less than 300
Public Sub LengthCountTest()
Dim rngCell             As Range
Dim intColTextLength    As Integer
Dim intRowCount         As Integer
Dim intColCount         As Integer
Dim aryColCount()       As Integer
Dim intColumns          As Integer
Dim intColNumber        As Integer
Dim strNextColumn       As String
Dim intMax              As Integer
'First set the print area to a named range
With ActiveSheet
    .Names.Add "ReportArea", _
        "=" & Range("Print_Area").Address
    intRowCount = .Range("ReportArea").Rows.Count
    Debug.Print "Number of Rows: " & intRowCount
    intColCount = .Range("ReportArea").Columns.Count
    Debug.Print "Number of Columns: " & intColCount
'End With

ReDim aryColCount(1 To intColCount)

intColumns = 1
    Do Until intColumns > intColCount
        With ActiveSheet
            strNextColumn = WhichColumn(intColumns)
            For Each rngCell In Range(strNextColumn & ":" & strNextColumn).Columns
                    intMax = fntMaxInCol(strNextColumn)
                Debug.Print intMax
            Next rngCell
            intColumns = intColumns + 1
        End With
End With

End Sub
Function fntMaxInCol(col As String) As Integer
'Finds the longest string in a column
Dim lngLastrow As Long
Dim i As Long
   lngLastrow = Range(col & Rows.Count).End(xlUp).Row
   fntMaxInCol = 0
   For i = 1 To lngLastrow
      If Len(Range(col & i).Value) > fntMaxInCol Then fntMaxInCol = Len(Range(col & i).Value)

End Function
Public Function WhichColumn(intColNumber As Integer) As String
'Determines which colum is next in the loop based on inter passed in
    Select Case intColNumber
        Case 1
            WhichColumn = "A"
        Case 2
            WhichColumn = "B"
        Case 3
            WhichColumn = "C"
        Case 4
            WhichColumn = "D"
        Case 5
            WhichColumn = "E"
        Case 6
            WhichColumn = "F"
    End Select
End Function

Open in new window

Question by:ssmith94015
  • 2
LVL 24

Accepted Solution

StephenJR earned 500 total points
ID: 35021851
Perhaps change line 46 to:

If Len(Range(col & i).Value) > fntMaxInCol And  Len(Range(col & i).Value)<300 Then
    fntMaxInCol = Len(Range(col & i).Value)
End If
LVL 22

Expert Comment

ID: 35021940
Or maybe search for something like the number of spaces (or words), like this:

if Len(Range(col & i).Value) > fntMaxInCol And UBound(Split(Range(col & i).Value, " "))< 5 then
   ' less that 5 "words" found so it must be a non-memo value
   fntMaxInCol = Len(Range(col & i).Value)
end If

Author Closing Comment

ID: 35021946
I never thought of limiting the value to less than 300, this is perfect.

Author Comment

ID: 35021966
rspahtiz, thank you for your attention. but the less than 300 works best for what I am trying to do.

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question