.Cells(xlDown) with one item in list

[hsummer]

I have a function that looks at a list of numbers, and I want to use .Cells(xlDown) to get to the bottom of the range.

When there is only one item, the xlDown goes to the bottom of the sheet. Then if I do a "For each c in ..." it takes a while.

What is an easy way to prevent that?

Thanks,
Harold

 

[xlbo]

Assign to a variable and test
dim lastRow as long
lastRow = activecell.end(xldown).row
If lastRow = 65536 then
'You only have 1 item
else
'You have more than 1 item
end if

HTH Rgds
~Geoff~

 

[Loomah]

This should get you started

It assumes data will be in A1

Sub marine()
Dim strCell As String
If IsEmpty(Range("A1").Offset(1, 0)) Then
    strCell = Range("A1").Address
Else: strCell = Range("A1").End(xlDown).Address
End If
MsgBox "Last Cell is " & strCell

End Sub

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[hsummer]

Thank you both.
Now it works.