Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro code to put series name next to individual line in line grap | Charts and Charting in Excel | |||
How do I make a line graph WITHOUT first line starting at zero? | Charts and Charting in Excel | |||
Make a line in a bar chart, and change color of any bars that exceed the line | Excel Discussion (Misc queries) | |||
How to make a button VBA code reference other VBA code subroutines??? | Excel Programming | |||
Code to make a New line in a message | Excel Programming |