How to find the last active cell in a range

Hi,

I have a range of cells which contain text and I need to be able to find the last cell in that range and delete the cells that I do not require. I need to do this for several ranges within the workbook so obviously i will need to loop the formula.

Does anyone have any ideas?

I"m not sure I understand the problem. But I recently had a similar problem where I needed it to look at a list and find the last non-zero number. I used the Match function to find it. that might work for you.
Chuck.

Yeah, i've tried Match and Offset but can not get them to work. Basically just need to know how to find the last active or last non blank cell in a range, then loop this to do it for several ranges throught the workbook.

Hi!

The following function is very useful and covers a wide spectrum of situations (whole sheets, individual rows or cols)


Public Function GetLast(Optional BookName As String, Optional SheetName As String, Optional Column As Boolean, Optional ColOrRow As String) As Integer

' GetLast Class method by Mark Bigelow

' mjbigelow at hotmail dot com

Dim objFind As Range

If BookName = "" Then
BookName = ActiveWorkbook.Name
End If

If SheetName = "" Then
SheetName = Workbooks(BookName).ActiveSheet.Name
End If

On Error Resume Next
If Column = True Then
If ColOrRow = "" Then
Set objFind = Workbooks(BookName).Sheets(SheetName).UsedRange
Else
Set objFind = Workbooks(BookName).Sheets(SheetName).Range(ColOrRow & ":" & ColOrRow)
End If
GetLast = objFind.Find(What:="*", searchdirection:=xlPrevious, searchOrder:=xlByColumns, LookIn:=xlValues).Column
If Err.Number <> 0 Then
GetLast = 1
Exit Function
End If
Else
If ColOrRow = "" Then
Set objFind = Workbooks(BookName).Sheets(SheetName).UsedRange
Else
Set objFind = Workbooks(BookName).Sheets(SheetName).Range(ColOrRow & ":" & ColOrRow)
End If
GetLast = objFind.Find(What:="*", searchdirection:=xlPrevious, searchOrder:=xlByRows, LookIn:=xlValues).Row
If Err.Number <> 0 Then
GetLast = 1
Exit Function
End If
End If
On Error GoTo 0

End Function

Call the function with r=GetLast (for last row in sheet)
or r=GetLast( , , , "A") for last row in col A
or c=GetLast( , , True, "15") for last column in row 15,

Result is a number which you can then use in the NameBox to go to the cell in question.

Alf

Hi!

Forgot one important bit of information:

r=GetLast( , , ,"D:G") will find the last row in columns D to G.

Alf