start point: formula in col. B
directions: go to col. A, step upward 'til reach non-blank cell
end point: use contents of $A# inside the formula

I can physically find the required data using Go To (or even [End] +
arrow-up key)
but how would one write a macro to do this?

Try something like

Dim Rng As Range
Dim S As String
Set Rng = Cells(Rows.Count, "A").End(xlUp)
S = CStr(Rng.Row)
Rng(1, 2).Formula = "=$A$" & S

Or, more compactly,

Cells(Rows.Count, "A").End(xlUp)(1, 2).Formula = _
"=$A$" & CStr(Cells(Rows.Count, "A").End(xlUp).Row)



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


"having eXceLed, I now want aXeS"
om wrote in
message
...
start point: formula in col. B
directions: go to col. A, step upward 'til reach non-blank cell
end point: use contents of $A# inside the formula

I can physically find the required data using Go To (or even
[End] +
arrow-up key)
but how would one write a macro to do this?

Range("B100").Select
set rng = Cells(ActiveCell.Row,1).End(xlup)
activecell.Formula = "=Sum(A1:" & rng.Address(0,0) & ")"

--
Regards,
Tom Ogilvy

"having eXceLed, I now want aXeS"
om wrote in message
...
start point: formula in col. B
directions: go to col. A, step upward 'til reach non-blank cell
end point: use contents of $A# inside the formula

I can physically find the required data using Go To (or even [End] +
arrow-up key)
but how would one write a macro to do this?