I think it depends on what you mean.
If I have a formula in A1:A100 and another in X1, what's the address of that
lastcell with a formula--is it A100 or X1?
But maybe...
Option Explicit
Sub testm()
Dim myRng As Range
Set myRng = Nothing
On Error Resume Next
Set myRng = Worksheets("Sheet1").Cells.SpecialCells(xlCellType Formulas)
On Error GoTo 0
If myRng Is Nothing Then
MsgBox "hey, no formulas"
Else
With myRng
'last area of this multiarea range
With .Areas(.Areas.Count)
'last cell in this last area
MsgBox .Cells(.Cells.Count).Address
End With
End With
End If
End Sub
But I wouldn't trust this kind of thing--who knows how excel will order those
areas.
If I know what I want, I'd look through rows or columns trying to find that last
formula.
wrote:
Using VBA
Trying to indentify the last cell on sheet that has a formula.
code fragment below
have tried
Set rge = Sh.UsedRange.SpecialCells(xlCellTypeFormulas,
xlCellTypeLastCell)
msgbox rge.Address
However I this returns the address of all formula cells
Any suggestions to get the address of the last cell with a formula ?
ie used range a1: j 100 , and last formula may be j 89
--
Dave Peterson