Return value from first row

K

Kent

Does anybody know how to return the value (maybe in a msgBox) from first row in the column where the curser is placed ? If I use "activecell.End(xlUp).value" then it will not go all the way to the top if there is some blank cell.
 
C

Chip Pearson

Kent,

If you really want row 1, use
MsgBox ActiveSheet.Cells(1, ActiveCell.Column).Value

If you want the first cell in the column containing data, use


Dim R As Range
Set R = ActiveSheet.Cells(1, ActiveCell.Column)
If R.Value = "" Then
Set R = R.End(xlDown)
End If
MsgBox R.Value


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


Kent said:
Does anybody know how to return the value (maybe in a msgBox)
Click to expand...
from first row in the column where the curser is placed ? If I
use "activecell.End(xlUp).value" then it will not go all the way
to the top if there is some blank cell.
 
B

Bob Phillips

Cells(1,activecell.column).select

for first row

cells(1,activecell.column).end(xldown).select

for first row with data

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Kent said:
Does anybody know how to return the value (maybe in a msgBox) from first
Click to expand...
row in the column where the curser is placed ? If I use
"activecell.End(xlUp).value" then it will not go all the way to the top if
there is some blank cell.
 
Top