I am really asking this for feedback but do have a question or two that I will try to include.  I had a CSV file that I imported into Excel to be able to read and review.  The data will actually be going to a MYSQL DB but that probably doesn't matter.  There are about 15 "fields" or columns (if you think about it as Excel shows it) and 170K records or rows.

To get ready to work with the data in the DB I wanted an idea of how many characters were in each field.  Specifically I wanted to know the max length I would need for a DB field.  Excel spaced the columns nicely but I wasn't sure how to find out how many characters each column needed or required.  So I used the VB Editor and made a little routine.  The code is below (see snippet).  It actually appears to work so I am feeling pretty proud (VBA is something I don't like to use at all and find very frustrating to work in even though I like VB, or at least vbscript).

My main question is do you see any problems or a way to do it better?  Specifically how I reference the cells, columns, rows and "range"?  I think the logic is about as good as can be but working with the "object" of a sheet and all of its elements, properties, etc is where I just lack expertise (and MS' Help seems especially hard to use as reference).

I had to use "CurrentRegion" to get the part of the sheet that was used (i.e. ignore the blank or unused columns to the right and rows below) but I didn't think I had to use it before.  I have only made couple but thought there was an easier way to select just the used cells and was frustrated until I found the CurrentRegion property(?).  Is there another that is often used or is the only other option specifically using cell references to set the range?

The only "bug" that I found when running it was the max character amount was place on the second row down, not the very lowest row.  Any idea why looking at my For loop and the line below?

Finally why couldn't I use For Each to loop through the Rows or Columns "collection"?  Specifically the columns since I changed the For Each loop for the rows because I wanted a "counter."  I was getting errors though and didn't understand why but they left when I changed to a "For i = 1" loop.

BTW, was I correct to start the loop at 1?  I couldn't find that for sure but thought the count of columns/rows started at 1.  Just was hoping to verify that.

Thanks!  Let me know if there is a question about this or you need any more info.

bol

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:

Attribute VB_Name = "Module1"
Sub getMaxLen()
    Dim col, i, line
    Dim myRange As Range
    Worksheets("Sheet1").Activate
    Set myRange = ActiveCell.CurrentRegion
    'MsgBox (myRange.Rows.Count & " - " & myRange.Columns.Count)
    'Exit Sub
    For col = 1 To myRange.Columns.Count
        If col > 16 Then Exit Sub
        'MsgBox (Cells(1, col).Value)
        'If Cells(1, col).Value = "" Then Exit Sub
        i = 0
        For line = 1 To myRange.Rows.Count
            If Len(Cells(line, col).Value) > i Then i = Len(Cells(line, col).Value)
        Next
        Worksheets("Sheet1").Cells((line + 1), col).Value = i
    Next
End Sub