Hey folks, I wrote this neato function, but it always takes a second or
two to complete all its loops. I sometimes run it on each page in a
workbook for various reasons and it slows me down.

The code starts analyzing the value in each cell from the bottom of a
worksheet, and gets the last line of data (using IsEmpty) in each
column. It does this for 100 columns but exits if it gets five blank
columns in a row.

I tried searching for a built-in function to accomplish the same task,
but could not. Anyone have any tips? I suppose I could write in a
couple of input boxes to have the user specify a smaller maximum line
to analyze, but I'm looking for something more elegant.

Function FindLastLine()
Dim x As Variant
Dim a, acount, maxx, c As Integer
On Error Resume Next

maxx = 0
For c = 1 To 100
a = 65537
Do
a = a - 1
x = Cells(a, c)
Loop Until IsEmpty(x) = False Or a = 1
If a maxx Then maxx = a
If a = 1 Then acount = acount + 1

'set the count threshold below to higher than 5 if your file has a lot
of blank lines
If acount 5 Then GoTo Exxxit
Next c
Exxxit:
FindLastLine = maxx
End Function


--
coctosten
------------------------------------------------------------------------
coctosten's Profile: http://www.excelforum.com/member.php...o&userid=28757
View this thread: http://www.excelforum.com/showthread...hreadid=484466