PDA

View Full Version : find last cell with formula



lior03
09-16-2007, 02:15 AM
hello
i was trying to use johnske's article about finding the last row in a sheet containing formula using find or special cells methods.
is there another way to find the last cell in a sheet with a formula?
thanks

Bob Phillips
09-16-2007, 02:19 AM
Why do you not want to use that method? The alternative is a long-winded check on every cell.

lior03
09-16-2007, 02:40 AM
how do you fell about:

Cells.SpecialCells(xlCellTypeFormulas, xlCellTypeLastCell).Activate

Bob Phillips
09-16-2007, 12:59 PM
What do you mean? I have no feelings apart from it doesn't do what you asked originally.

lior03
09-17-2007, 06:54 AM
hello
i found this two macros in johnske article.they both return the same result.why?

Sub Find_LastRowxlValues()
On Error GoTo Finish
MsgBox "Last row is row " & Cells.Find("*", _
SearchOrder:=xlByRows, LookIn:=xlValues, _
SearchDirection:=xlPrevious).EntireRow.row
Exit Sub
Finish:
MsgBox "No values found"
End Sub
Sub Find_LastRowxlFormulas()
On Error GoTo Finish
MsgBox "Last row is-" & Cells.Find("*", _
SearchOrder:=xlByRows, LookIn:=xlFormulas, _
SearchDirection:=xlPrevious).EntireRow.row, vbExclamation, "last cell with a 4mula:"
Exit Sub
Finish:
MsgBox "No formulas or values found - " & err.number
End Sub


should'nt be any difference between a cell with a constant and a cell with a formula?what is ther lookin suppose to do?
thanks

lior03
09-17-2007, 11:41 AM
my solution:

On Error GoTo Finish
MsgBox "Last formula row is-" & Cells.Find("=", _
SearchOrder:=xlByRows, LookIn:=xlFormulas, _
SearchDirection:=xlPrevious).EntireRow.row, vbExclamation, "last cell with a 4mula:"
Exit Sub
Finish:
MsgBox "No formulas or values found - " & err.number, vbCritical, "error"