Last numeric cell

  • Thread starter Thread starter shawnews
  • Start date Start date
S

shawnews

Trying to use a macro to find the last NUMERIC cell after a range has been
sorted.
ie:

using the following in a macro----
Range(Range("l25"), Range("l25").End(xlDown)).Select

column l:

1
5
8
6
1 I want to find this cell
blank cell
blank cell
blank
and so on

TOS
TOS
TOS
Tos The above macro finds this cell.

Any help appreciated.
 
Your data as presented is not sorted...
"1" appears in two non-adjacent cells and the blank cells are not at the bottom.
The following works, but only in a single sorted column...

Dim rng As Range
Set rng = Selection.SpecialCells(xlCellTypeConstants, xlNumbers)
Set rng = rng(rng.Count)
MsgBox rng.Address

Another way would be to loop from the bottom up looking for any number.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"shawnews" <[email protected]>
wrote in message
Trying to use a macro to find the last NUMERIC cell after a range has been
sorted.
ie:
using the following in a macro----
Range(Range("l25"), Range("l25").End(xlDown)).Select

column l:

1
5
8
6
1 I want to find this cell
blank cell
blank cell
blank
and so on

TOS
TOS
TOS
Tos The above macro finds this cell.

Any help appreciated.
 
I'm confused about what you're selecting, but if your numbers are values (not
formulas), you can use something like:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim LastNumericCell As Range
Dim JustNumberConstants As Range

Set wks = ActiveSheet

Set JustNumberConstants = Nothing
On Error Resume Next
Set JustNumberConstants _
= wks.Range("a:a").Cells.SpecialCells(xlCellTypeConstants, xlNumbers)
On Error GoTo 0

If JustNumberConstants Is Nothing Then
MsgBox "No number constants"
Else
With JustNumberConstants
With .Areas(.Areas.Count)
Set LastNumericCell = .Cells(.Cells.Count)
End With
End With

MsgBox LastNumericCell.Address & vbLf & LastNumericCell.Value
End If
End Sub

If your numbers are all the results of formulas, you could use:

Set JustNumberConstants _
= wks.Range("a:a").Cells.SpecialCells(xlCellTypeFormulas, xlNumbers)

It gets a bit uglier if you have a mixture.
 
Back
Top