Sandra Smith
asked on
Max data return for lengh, but need max less than 300
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I never thought of limiting the value to less than 300, this is perfect.
ASKER
rspahtiz, thank you for your attention. but the less than 300 works best for what I am trying to do.
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