Find Last Line Code: Let's Make This Better

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

Function LastRow() As Long
LastRow = Cells.Find(What:="*", _
After:=Range("A1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
End Function

--

HTH

RP
(remove nothere from the email address if mailing direct)


"coctosten" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

If you go to the bottom of column A (say A65536), you can find the last
non-empty cell by hitting the End key (on the keyboard), then the uparrow.

You can do the same thing in code:

dim LastRow as long
dim iCol as long

icol = 5 'something to test with
with activesheet
lastrow = .cells(.rows.count,icol).end(xlup).row
end with

You could loop through as many columns that you want, too.

dim LastRow as long
dim iCol as long
dim MaxRow as long
maxrow = 1
with activesheet
for icol = 1 to 5
lastrow = .cells(.rows.count,icol).end(xlup).row
if lastrow > maxrow then
maxrow = lastrow
end if
next icol
end with

=========
Or you could use another method. Debra Dalgleish shows some code that you could
use:

http://contextures.com/xlfaqApp.html#Unused

coctosten wrote:
>
> 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

--

Dave Peterson

Try:
ActiveSheet.UsedRange.Address()

From the result you should be able to determine the last row and/or column.

"coctosten" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>