Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-29-2010, 01:36 PM
rdcsfd rdcsfd is offline lastincolumn function Windows 7 lastincolumn function Office 2007
Novice
lastincolumn function
 
Join Date: Jan 2010
Posts: 3
rdcsfd is on a distinguished road
Default lastincolumn function

lastincolumn is a walkenbach function that returns the value for the last filled cell in a column. works well and is very fast.

Function LASTINCOLUMN(rngInput As Range)
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rngInput.Columns(1).EntireColumn
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINCOLUMN = WorkRange(i).Value
Exit Function
End If
Next i
End Function

the following function, lastinrange, is a modification of lastincolumn to return the location/range of the last filled cell in a column, necessary to use with offset function. But, it is excruciatingly slow. does anyone know why?

Function LastInRange(InputRange As Range)
Dim CellCount As Long
Dim i As Long
CellCount = InputRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(InputRange(i)) And IsNumeric(InputRange(i)) Then
Set LastInRange = InputRange(i)
Exit Function
End If


Next i
LastInRange = ""
End Function
Reply With Quote
  #2  
Old 01-30-2010, 03:16 PM
BjornS BjornS is offline lastincolumn function Windows Vista lastincolumn function Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

Hi,
I get i little puzzled when I see you function LastInRange.
Isn't your intention to get an answer of let's say 50, it the last number if 50 rows down?
Is there any special reason to why you check for numeric values only?
What is the explanation to why you have written LastInRange = "" at the end? This will always give an empty answer back.

I tried this code instead (see below). Both this and yours is fast. How long time does the function take when you use it?

Best regards
Bjorn


Function LastInRange2(InputRange As Range)
Dim CellCount As Long
Dim i As Long
CellCount = InputRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(InputRange(i)) And IsNumeric(InputRange(i)) Then
LastInRange2 = i
Exit Function
End If
Next i
End Function
Reply With Quote
  #3  
Old 01-30-2010, 03:28 PM
zyzzyva57 zyzzyva57 is offline lastincolumn function Windows 7 lastincolumn function Office 2007
Expert
 
Join Date: Mar 2009
Location: Dawsonville, Ga (NE of Atl)
Posts: 355
zyzzyva57 is on a distinguished road
Default

Here are some ideas to try:

Function LastInColumn(a As Range)
LastInColumn = Range("a1").Cells(1048576, a.Column).End(xlUp).Address
End Function

====================

Function LastInRange(InputRange As Range)
LastInRange = InputRange.Cells(InputRange.SpecialCells(xlLastCel l).Row +
1, InputRange.Column).End(xlUp)
End Function

Or shorten it by using the With statement:

Function LastInRange(InputRange As Range)
With InputRange
LastInRange = .Cells(.SpecialCells(xlLastCell).Row + 1, .Column).End(xlUp)
End With
End Function
Reply With Quote
  #4  
Old 01-31-2010, 11:42 AM
rdcsfd rdcsfd is offline lastincolumn function Windows 7 lastincolumn function Office 2007
Novice
lastincolumn function
 
Join Date: Jan 2010
Posts: 3
rdcsfd is on a distinguished road
Default

Hi Bjorn:

Thanks for the reply. I did not write that function. I found it on the Internet. It was so slow I turned off automatic calculation. This on a Dell workstation with twin Xeon processors and 8GB of memory. I removed LastInRange="" and that improved the speed, but it still takes 2-3 minutes to calculate this function 10 times on a worksheet.

Are you saying the LastInRange function I posted is working fast on your machine?
Reply With Quote
  #5  
Old 01-31-2010, 11:49 AM
zyzzyva57 zyzzyva57 is offline lastincolumn function Windows 7 lastincolumn function Office 2007
Expert
 
Join Date: Mar 2009
Location: Dawsonville, Ga (NE of Atl)
Posts: 355
zyzzyva57 is on a distinguished road
Default

I was just tinkering around with the code hoping I might stumble on some clue that might help you
Reply With Quote
  #6  
Old 01-31-2010, 12:45 PM
rdcsfd rdcsfd is offline lastincolumn function Windows 7 lastincolumn function Office 2007
Novice
lastincolumn function
 
Join Date: Jan 2010
Posts: 3
rdcsfd is on a distinguished road
Default

Sorry, but I could not get any of your ideas to work.
Reply With Quote
  #7  
Old 01-31-2010, 04:27 PM
BjornS BjornS is offline lastincolumn function Windows Vista lastincolumn function Office 2003
Competent Performer
 
Join Date: Jan 2010
Location: Sweden
Posts: 116
BjornS is on a distinguished road
Default

Quote:
Originally Posted by rdcsfd View Post
Hi Bjorn:

Thanks for the reply. I did not write that function. I found it on the Internet. It was so slow I turned off automatic calculation. This on a Dell workstation with twin Xeon processors and 8GB of memory. I removed LastInRange="" and that improved the speed, but it still takes 2-3 minutes to calculate this function 10 times on a worksheet.

Are you saying the LastInRange function I posted is working fast on your machine?
yes, it works very fast on my machine. Just about a second (or even less) for one update.

Could you please answer the questions in my previous reply, in order to understand what result you want back (since my function and your function are returning different things).

Could you perhaps also post your Excel sheet (or at least relevant parts of it), and let me try if it is slow also on my computer?

Kind regards
Bjorn
Reply With Quote
  #8  
Old 02-08-2012, 07:50 AM
it4728 it4728 is offline lastincolumn function Windows Vista lastincolumn function Office 2007
Novice
 
Join Date: Feb 2012
Posts: 1
it4728 is on a distinguished road
Default lastincolumn function troubleshoot

Hi,

I am facing a problem whereby i am trying to use the lastincolumn function to copy the last value in a column of a workbook into a new workbook. It requires me to open the workbook that I am copying from for it to work or to be updated. Is it possible to use the lastincolumn function without the need to open the workbook that i am copying, that is, to get the last value in a specific column in a closed workbook?
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Cross-reference function with Visio? Barometer Word 0 11-04-2009 12:38 PM
Outlook 2003/ Send to function ptservice Outlook 0 09-24-2009 07:09 AM
lastincolumn function Help! for using an appropriate function/Macro in Excel pawan2paw Excel 1 06-04-2009 12:28 PM
lastincolumn function Need help on what function to use??? Primeraman Excel 1 06-13-2006 10:16 AM
Help with Combination function sanasath Excel 0 12-13-2005 07:24 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft