#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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? |
#5
|
|||
|
|||
I was just tinkering around with the code hoping I might stumble on some clue that might help you
|
#6
|
|||
|
|||
Sorry, but I could not get any of your ideas to work.
|
#7
|
|||
|
|||
Quote:
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 |
#8
|
|||
|
|||
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? |
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 |
Help! for using an appropriate function/Macro in Excel | pawan2paw | Excel | 1 | 06-04-2009 12:28 PM |
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 |