MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Please Help 3rd Post


Posted by Roy Brunt on August 17, 2001 12:05 PM

I am using the following code to find the last cell on a worksheet.
Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious).Select

This used to work ok until i added formula's to the sheet and autofilled them. Now the code is returning the last cell with a formula in it instead of the last cell with actual data in it.

Does anyone know of a way around this so that I can keep my formula's but they are ignored when searching for the last cell containing data

Thanks in advance

Roy


Posted by Russell Hauf on August 17, 2001 12:50 PM

Try this:

Cells.Find(What:="*", After:=[A1], _
SearchDirection:=xlPrevious, LookIn:=xlValues).Select


Hope this helps,

Russell

Posted by Richie on August 17, 2001 12:55 PM

Roy,

I'm new to all this VBA stuff myself, but I had already discovered the following information at the Ozgrid site before I read your post. Hope it helps.

Richie.

Find the last Row, Column or Cell
You can use Edit>Go to-Special-Last cell to try and find the last cell in
the active sheet, but it is not very reliable. The reason is two-fold:


1. The last cell is only re-set when you save. This means if you enter any
number or text in say, cell A10 and A20 of a new Worksheet, then delete
the content of A20, the go to special will keep taking you to A20, until
you save.


2. It picks up cell fomatting. Let's say you enter any text or number in
cell A10 and then enter a valid date in cell A20 of a new Worksheet. Now
delete the date in cell A20 and save. The go to special will still take
you to A20. This is because entering a date in A20 has caused Excel to
automatically format the cell from "General" to a Date format. To stop
from going to A20 you will have to use Edit>Clear>All.


So when using VBA you cannot rely on a user to do this for you. Below are
three methods that will find the "LastRow", "LastColumn" and the "LastCell"


Sub FindLastRow()
Dim LastRow As Long
If WorksheetFunction.CountA(Cells) > 0 Then


'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

MsgBox LastRow
End If
End Sub


Sub FindLastColumn()
Dim LastColumn As Integer
If WorksheetFunction.CountA(Cells) > 0 Then


'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

MsgBox LastColumn
End If
End Sub


Sub FindLastCell()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As Range
If WorksheetFunction.CountA(Cells) > 0 Then


'Search for any entry, by searching backwards by Rows.
LastRow = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

'Search for any entry, by searching backwards by Columns.
LastColumn = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

MsgBox Cells(LastRow, LastColumn).Address


End If
End Sub


Finding the last cell in a known Column or Row.
While we could use a variation of the above methods to find the last cell
in a known Row or Column, we can save ourselves a bit of typing by using
these two methods below.


Sub LastCellInColumn()
Dim LastCell As Range


If WorksheetFunction.CountA(Columns(1)) > 0 Then
Set LastCell = Range("65536").End(xlUp)
MsgBox LastCell.Address
End If


End Sub


Sub LastCellInRow()
Dim LastCell As Range


If WorksheetFunction.CountA(Rows(1)) > 0 Then
Set LastCell = Range("IV1").End(xlToLeft)
MsgBox LastCell.Address
End If


End Sub

Posted by Ivan F Moala on August 17, 2001 9:47 PM

What you have and what you are after are diff.
Your macro searchs as it is specified and doesn't
distinquish between data types.
You need to look @ special cell types eg;

This will search for Constants eg
Text, numbers, logical, errors.

Sub FindLastDataCell()
Dim DataRg As Range

Set DataRg = Cells.SpecialCells(2, 23)
DataRg.Find(What:="*", After:=DataRg(1), SearchDirection:=xlPrevious).Select
End Sub

Ivan