Solved

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

Posted on 2011-03-02
4
250 Views
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
    Loop
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)
   Next

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

0
Comment
Question by:ssmith94015
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 500 total points
Comment Utility
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
0
 
LVL 22

Expert Comment

by:rspahitz
Comment Utility
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
0
 

Author Closing Comment

by:ssmith94015
Comment Utility
I never thought of limiting the value to less than 300, this is perfect.
0
 

Author Comment

by:ssmith94015
Comment Utility
rspahtiz, thank you for your attention. but the less than 300 works best for what I am trying to do.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The canonical version of this article is on my web site here: http://iconoun.com/articles/collisions/ A companion presentation is available here: http://iconoun.com/articles/collisions/Unicode_Presentation.pdf
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

772 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now