Aladin Akyurek
Posted by Phil on August 10, 2001 3:26 AM
You answered a question for me about 3 weeks ago regarding finding the last cell on a page by giving me a function that you had... since my computer h/d has become corrupt and wondered if i could get it again....?
Thanxs 4 ur help
Phil
Posted by Aladin Akyurek on August 10, 2001 4:58 AM
Last Cell In Use
If the column (or the row) whose last cell you want to determine consists of numeric data (whether or not interspersed with blanks), you can use the MATCH function:
=MATCH(1.0E+30,A:A) [ for columns.]
=MATCH(1.0E+30,1:1) [ for rows; 1:1 means row 1 ]
will give you the location of the last cell in use (which has a numeric value in it).
Note. These formulas expect that there will not be a numeric value as big as 1.0E+30 among the numeric values of the column/row you're testing. MATCH, failing to find an approximate match, returns the location (not the ref) of the last cell with a numeric value, which is precisely the one you want.
If interested in the address (ref) of the cell, expand the formula as follows:
=ADDRESS(MATCH(1.0E+30,A:A),COLUMN(A:A)) [ for columns ]
=ADDRESS(ROW(1:1),MATCH(1.0E+30,1:1)) [ for rows ]
If interested in the value of the last cell in use, wrap the formulas that contain the ADDRESS function with INDIRECT:
=INDIRECT(ADDRESS(...))
What if the range you interested in consists of solely of text values (interspersed with blanks)?
The above scheme will not work. Although there are other possibilities, I'll simply post the following (hassle-free) UDF's:
Option Explicit
Function LASTINCOLUMN(rng As Range)
' From J. Walkenbach, modified to return the row number [ Aladin Akyürek, Aug 8, 01 ]
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rng.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).Row
Exit Function
End If
Next i
End Function
Function LASTINROW(rng As Range) As Variant
' From J. Walkenbach, modified to return the row number [ Aladin Akyürek, Aug 8, 01 ]
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rng.Rows(1).EntireRow
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, _
WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINROW = WorkRange(i).Column
Exit Function
End If
Next i
End Function
Place this code in a "module".
Usage:
=LASTINCOLUMN(A:A)
=LASTINROW(1:1)
will give you the location of the last cell in use (regardless whether it contains a text value or a numeric value).
You can then do the same things with the location value that is returned by these UDFs as with that returned by the MATCH value as described above.
If your needs are still not met, please post your specs.
Aladin
Posted by Phil on August 10, 2001 5:18 AM
Re: Last Cell In Use
The last functions the one - thanxs
- how could i isplay the lastincolumns value in a msgBox in VB
thanxs again
phil
Posted by Aladin Akyurek on August 10, 2001 8:21 AM
For that one, you need a VBA programmer (NT)
Posted by Ivan F Moala on August 14, 2001 8:46 PM
Re: Last Cell In Use
Function LASTINROW(rng As Range) As Variant
' From J. Walkenbach, modified to return the row number [ Aladin Akyürek, Aug 8, 01 ]
Dim WorkRange As Range
Dim i As Integer, CellCount As Integer
Application.Volatile
Set WorkRange = rng.Rows(1).EntireRow
Set WorkRange = Intersect(WorkRange.Parent.UsedRange, _
WorkRange)
CellCount = WorkRange.Count
For i = CellCount To 1 Step -1
If Not IsEmpty(WorkRange(i)) Then
LASTINROW = WorkRange(i).Column
msgbox LASTINROW
Exit Function
End If
Next i
End Function
Ivan
