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


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

Find Last Line Code: Let's Make This Better
 

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" wrote
in message ...

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

Find Last Line Code: Let's Make This Better
 

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

Find Last Line Code: Let's Make This Better
 

Try:
ActiveSheet.UsedRange.Address()

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

"coctosten" wrote
in message ...

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