MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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