Solved

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

Posted on 2011-03-02
4
263 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
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
0
 
LVL 22

Expert Comment

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

Author Closing Comment

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

Author Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
copy the content of word to outlook 3 48
Excel copy picture into Outlook email 7 44
Mac Excel column treating text as date 2 30
Auto Adjust Percent rate 5 26
Lync meeting or Lync conferencing is what many organizations would like to deploy to allow them save money. But companies are now giving up for various reasons, one of which is that they cannot join external meetings (non-federated company meetings)…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

919 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

19 Experts available now in Live!

Get 1:1 Help Now