select last column and last nonblank cell

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AHizon - 12 Oct 2007 18:58 GMT

Hello,

I recorded a Macros & modified slightly to select the last column and last
nonblank cell in that column, but I would like to know how to find the last
nonblank column and last nonblank cell in that column to sum all the column
after the header. Below is the coding that I have:

Range("A1").Select
Selection.End(xlToRight).Select
Selection.End(xlDown).Select
Range("K" & Rows.Count).End(xlUp).Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=SUM(R[-27]C:R[-1]C)-R[-27]C[-8]"

I want Column "K" to be dynamic so that it finds the last column instead of
hard coding to only Column K. I would also like the formula to be dynamic so
that it sums all the cells above the last nonblank cell minus cell(C2). Can
anyone assist me with this?

Thanks,
Abigail

Reply to this Message

JW - 12 Oct 2007 19:48 GMT

This will find the last col, the last used row in the column, and
place a formula in the cell below that one that sums all of the cells
above it (not counting the header row). That what you are after?
Sub thisHere()
lCol = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
With Cells(Rows.Count, lCol).End(xlUp)
.Offset(1, 0).FormulaR1C1 = _
"=SUM(R[-" & .Row - 1 & "]C:R[-1]C)"
End With
End Sub

Not really sure what you meant by

> Hello,
>
[quoted text clipped - 20 lines]
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200710/1

Reply to this Message

AHizon - 12 Oct 2007 22:27 GMT

That worked...Thanks alot!!

>This will find the last col, the last used row in the column, and
>place a formula in the cell below that one that sums all of the cells
[quoted text clipped - 16 lines]
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.aspx/excel-new/200710/1

Reply to this Message

AHizon - 15 Oct 2007 19:58 GMT

Sorry to bother, want to modify the sum formula that you assisted below so
that it sums the cells above minus cell=C2? Is there any way to hard code
the 2nd part so that it's always subtracting from C2 and no other cells?
When I used a Macro to record this function it gives me the following and I
modified it a bit but I'd like it so that it's always Column = C and Row = 2
because the number of columns changes in between column C and the last column
depending on the sheet.
lCol = Cells.Find(What:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
With Cells(Rows.Count, lCol).End(xlUp)
.Offset(1, 0).FormulaR1C1 = _
"=SUM(R[-" & .Row - 1 & "]C:R[-1]C)-R[-" & .Row - 1 & "]C[-8]"
I would like to change C[-8] to be always Column C and not 8 columns to the
left of the cell with the formula. Any assistance would be appreciated.

Reply to this Message

JW - 16 Oct 2007 23:59 GMT

> Sorry to bother, want to modify the sum formula that you assisted below so
> that it sums the cells above minus cell=C2? Is there any way to hard code
[quoted text clipped - 35 lines]
> --
> Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-new/200710/1

one way:
Sub thisHere()
lCol = Cells.Find(what:="*", After:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
With Cells(Rows.Count, lCol).End(xlUp)
.Offset(1, 0).FormulaR1C1 = _
"=SUM(R[-" & .Row - 1 & "]C:R[-1]C)"
.Offset(1, 0).Formula = _
.Offset(1, 0).Formula & "-$C$2"
End With
End Sub

Reply to this Message

Rate this thread: