Insert row with max length found in Column

Hi,

I have a bunch of CSV or XLS files. I need to find the max length of what is in each column and insert it as a column header for each file. The number of columns per file vary as do the number of rows. This will be either added as the top CSV row or to XLS that will be converted to CSV.

Try this macro
It inserts a row at row 1
places entry equal to number of rows & the word row. example a1 = 59 rows

Sub AddHeader()
Dim iCol As Integer
Dim lRow As Long
Dim iForCol As Integer

Rows("1:1").Insert Shift:=xlDown
iCol = .Cells.Find(what:="*", searchorder:=xlByColumns, _
searchdirection:=xlPrevious).Column
For iForCol = 1 To iCol Step 1
lRow = Cells(Rows.Count, iCol).End(xlUp).Row
Cells(1, iCol).Value = lRow & " Rows"
Next iForCol
End Sub

Thank you. Your code did help get the ball rolling. This is what I have. Wondering if there is a more efficient way than looping through all the rows, brute force.

' Insert the row
Rows("1:1").Insert Shift:=xlDown

' Get the max
MaxCol = ActiveSheet.UsedRange.Columns.Count
MaxRow = ActiveSheet.UsedRange.Rows.Count

For ForCol = 1 To MaxCol Step 1
MaxLen = 0

For ForRow = 3 To MaxRow Step 1
If Len(Cells(ForRow, ForCol).Value) > MaxLen Then
MaxLen = Len(Cells(ForRow, ForCol))
End If

Next ForRow

'WorksheetFunction.Range()
'MaxLen = WorksheetFunction.Max( Cell(ForCol, 2)

Cells(1, ForCol).Value = MaxLen

Next ForCol

Nope. You have to go through every cell that has information in it.

This might give you a bit of a speed increase:

Please Login or Register  to view this content.

Scott

Looping through the rows took way too long. I have modified the code to this. Which runs in a blink of an eye compared to before.

Is there a way to specify Range() instead of me building it?


MaxCol = objWorkSheet.UsedRange.Columns.Count
MaxRow = objWorkSheet.UsedRange.Rows.Count

For ForCol = 1 To MaxCol Step 1
MaxLen = 0
ColLetter = ConvertColumnNumberToLetter(ForCol)

objWorkSheet.Cells(1, ForCol).FormulaArray = "=MAX(LEN(" + ColLetter + "3:" + ColLetter + CStr(MaxRow) + "))"

Next ForCol