VBA for Last Cell in Formula Range
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 |
VBA for Last Cell in Formula Range
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 In article .com, 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 |
VBA for Last Cell in Formula Range
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 |
VBA for Last Cell in Formula Range
just pick the last item from the range:
Sub last_formula() Set rge = ActiveSheet.UsedRange.SpecialCells(xlCellTypeFormu las, xlCellTypeLastCell) f = Split(rge.Address, ",") u = UBound(f) MsgBox (f(u)) End Sub -- Gary''s Student - gsnu200719 " 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 |
All times are GMT +1. The time now is 11:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com