VBA for Last Cell in Formula Range

[dhockin]

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

 

[JE McGimpsey]

One way:

On Error Resume Next
With Sh.Cells.SpecialCells(xlCellTypeFormulas)
With .Areas(.Areas.Count)
Set rge = .Cells(.Count)
End With
End With
On Error GoTo 0
If Not rge Is Nothing Then MsgBox rge.Address

 

[Dave Peterson]

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(xlCellTypeFormulas)
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.

 

[Guest]

just pick the last item from the range:

Sub last_formula()
Set rge = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormulas,
xlCellTypeLastCell)
f = Split(rge.Address, ",")
u = UBound(f)
MsgBox (f(u))
End Sub