reference a cell above the last cell

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I reference the cell above the last cell in a column?? I know how to
figure out the last cell, but I'm not sure how to subtract the last cell by
the one right above it.
 
Conny,

Try something like the following. Change the "A" to whatever column you want
to test.

Dim R As Range
Set R = ActiveSheet.Cells(Rows.Count, "A").End(xlUp)
If R.Row <> 1 Then
Set R = R(0, 1)
End If
R.Select


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
By the way this is for automation, so i'm making formulas, where one
references the last cell and this last cell will change every week, so I want
to figure out how to make it so I can subtract the last cell by the number in
the cell just above.
 
Check the answers to your previous post.

Please stay in your original thread to avoid others from duplicating and
wasting their efforts!
 
sorry... but where do i go to do all this... my column is t, so that's the
column i would use, I understand that part. But where do I do this dim R and
Set R= stuff. If you could help me out with this I would really appreciate
it. Thanks.
 
The code I posted creates a variable named 'R' of type Range and then sets
that variable to point to the cell above the last cell. Once you have that
reference, you can get its address with the Address property, read or change
the cell value with the Value property, or any of many other operations. If
you are building a formula, you will probably want to get the Address of R
and put that in your formula.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting
www.cpearson.com
(email on the web site)
 
Back
Top