Figuring out MAX LEN in a given column
in [Programming]
|
Prev: How to map a shortcut key to a routine in COM AddIn for Excel 2007
Next: Open files while login, Pls help
From: Maki on 12 May 2010 00:15 Hi. I'm trying to redesign a database, originally done in Access 2003, so exported tables to Excel to analyse existing records, and in this instance, maximum LEN in each column that's text. I want to program a macro which: 1. asks, via InputBox, which column to analyse; 2. starting from the row after the header, counts LEN for the record and moves on to the next row; and 3. returns, via MsgBox, MAX value amongst all LEN counted. I've been trying to teach myself Excel VBA programming by following John Walkenbach's "Excel 2007 Power Programming with VBA" but I'm obviously not "getting it" and can't even program to do COUNTA, starting with InputBox and ending with the value shown with MsgBox. Please help! -- Maki @ Canberra.AU
From: Jacob Skaria on 12 May 2010 00:58 Try the below macro.. Sub MyMacro() Dim lngRow As Long, varCol As Variant, intMaxLen As Integer varCol = InputBox("Enter the column to analyse") If IsNumeric(varCol) Then varCol = CLng(varCol) For lngRow = 2 To Cells(Rows.Count, varCol).End(xlUp).Row If Len(Cells(lngRow, varCol)) > intMaxLen Then intMaxLen = Len(Cells(lngRow, varCol)) End If Next MsgBox intMaxLen End Sub If you are interested in a formula try the below. Please note that this is an array formula. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula>}" =MAX(LEN(D2:D1000)) -- Jacob (MVP - Excel) "Maki" wrote: > Hi. > > I'm trying to redesign a database, originally done in Access 2003, so > exported tables to Excel to analyse existing records, and in this instance, > maximum LEN in each column that's text. > I want to program a macro which: > 1. asks, via InputBox, which column to analyse; > 2. starting from the row after the header, counts LEN for the record and > moves on to the next row; and > 3. returns, via MsgBox, MAX value amongst all LEN counted. > > I've been trying to teach myself Excel VBA programming by following John > Walkenbach's "Excel 2007 Power Programming with VBA" but I'm obviously not > "getting it" and can't even program to do COUNTA, starting with InputBox and > ending with the value shown with MsgBox. > > Please help! > -- > Maki @ Canberra.AU
From: Maki on 13 May 2010 08:31
Thank you so much, Jacob. It worked treat! I thought I needed to use Application.WorksheetFunction.Max() but didn't know quite how to make it work. Yours is heaps simpler and effective! Thanks again. -- Maki @ Canberra.AU "Jacob Skaria" wrote: > Try the below macro.. > > Sub MyMacro() > Dim lngRow As Long, varCol As Variant, intMaxLen As Integer > > varCol = InputBox("Enter the column to analyse") > If IsNumeric(varCol) Then varCol = CLng(varCol) > > For lngRow = 2 To Cells(Rows.Count, varCol).End(xlUp).Row > If Len(Cells(lngRow, varCol)) > intMaxLen Then > intMaxLen = Len(Cells(lngRow, varCol)) > End If > Next > > MsgBox intMaxLen > End Sub > > If you are interested in a formula try the below. Please note that this is > an array formula. You create array formulas in the same way that you create > other formulas, except you press CTRL+SHIFT+ENTER to enter the formula. If > successful in 'Formula Bar' you can notice the curly braces at both ends like > "{=<formula>}" > > =MAX(LEN(D2:D1000)) > > -- > Jacob (MVP - Excel) > > > "Maki" wrote: > > > Hi. > > > > I'm trying to redesign a database, originally done in Access 2003, so > > exported tables to Excel to analyse existing records, and in this instance, > > maximum LEN in each column that's text. > > I want to program a macro which: > > 1. asks, via InputBox, which column to analyse; > > 2. starting from the row after the header, counts LEN for the record and > > moves on to the next row; and > > 3. returns, via MsgBox, MAX value amongst all LEN counted. > > > > I've been trying to teach myself Excel VBA programming by following John > > Walkenbach's "Excel 2007 Power Programming with VBA" but I'm obviously not > > "getting it" and can't even program to do COUNTA, starting with InputBox and > > ending with the value shown with MsgBox. > > > > Please help! > > -- > > Maki @ Canberra.AU |