Last Row

[scottnshelly]

Please translate the following commands into working code.

find the last row
go down one more row
go to column A, type "TOTAL"
go to columns d:l and insert formula =sum(counta(d3:d*))
range (a1) select

* = the row above current row

 

[Bryan Kelly]

This finds the last row as part of adding a new column and extending a
formula down to the last row.

Sub Add_Elapsed_Time_Column()
'
Dim nLastRow As Long
' Find the last row on data in column C
nLastRow = Range("C" & Rows.Count).End(xlUp).Row
' build a variable representing the last cell in the column
nTheRange = "C4:C" & nLastRow
'
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "0.0"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Elapsed Time"
Range("C2").Select
ActiveCell.FormulaR1C1 = "0"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C + 0.1"
Range("C3").Select
Selection.copy
Range(nTheRange).Select
ActiveSheet.Paste
End Sub

 

[scottnshelly]

Thanks Bryan,
That worked great. Instead of a formula filling down the column,
need to fill a formula across the row below the last row.
Now i know how to find the last row with your code, but i need the las
part to work a little differently.
Thanks

 

[William]

Hi scottnshelly

Assuming you want to find the last row with data in column A and use that as
your starting point, try...

Sub test()
Dim r As Range
Set r = Range("A65000").End(xlUp).Offset(1, 0)
r = "Total"
Range(r.Offset(0, 3), r.Offset(0, 11)).FormulaR1C1 = _
"=COUNTA(R2C:OFFSET(RC,-1,0))"
Range("A1").Select
End Sub

--
XL2002
Regards

William

(e-mail address removed)

message | Please translate the following commands into working code.
|
| find the last row
| go down one more row
| go to column A, type "TOTAL"
| go to columns d:l and insert formula =sum(counta(d3:d*))
| range (a1) select
|
| * = the row above current row.
|
|
| ---
| Message posted
|

 

[scottnshelly]

thank a lot William,
that worked perfectly!
now, i guess i want something that will clear off the row that tha
macro added when the user goes to add another agent. so somethin
like:
range(a4:a).select
search for 'total'
clear off that row

thanks

 

[William]

Hi scottnshelly

Glad to have helped.

If the last cell in Column A is "Total", then this will delete the entire
row.

Sub test()
If Range("A65000").End(xlUp) = "Total" Then _
Range("A65000").End(xlUp).EntireRow.Delete
End Sub


--
XL2002
Regards

William

(e-mail address removed)

message | thank a lot William,
| that worked perfectly!
| now, i guess i want something that will clear off the row that that
| macro added when the user goes to add another agent. so something
| like:
| range(a4:a).select
| search for 'total'
| clear off that row
|
| thanks.
|
|
| ---
| Message posted
|

 

[Guest]

Bryan,

I am trying your macro below to do something similar, but am running into a
"Compile Error: Sub or Function not defined" error. I am creating a formula
in cell F2, want to copy this formula down to the last used row, then cutting
and pasting these values over to the corresponding cells in column D The
macro so far, with the error on the 9th row listed:

Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[1]"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/R2C[1]"

Dim nLastRow As Long
' Find the last row on data in column F
nLastRow = Range("F3" & Rows.Count).End(xlUp).Row
' build a variable representing the last cell in the column
nTheRange = F3: F " & nLastRow"
Range("F2").Select
Selection.Copy
Range(nTheRange).Select
ActiveSheet.Paste
nTheRange = F2: F " & nLastRow"
Range(nTheRange).Select
Selection.Copy

Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False


Any help?

This finds the last row as part of adding a new column and extending a
formula down to the last row.

Sub Add_Elapsed_Time_Column()
'
Dim nLastRow As Long
' Find the last row on data in column C
nLastRow = Range("C" & Rows.Count).End(xlUp).Row
' build a variable representing the last cell in the column
nTheRange = "C4:C" & nLastRow
'
'
Columns("C:C").Select
Selection.Insert Shift:=xlToRight
Selection.NumberFormat = "0.0"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Elapsed Time"
Range("C2").Select
ActiveCell.FormulaR1C1 = "0"
Range("C3").Select
ActiveCell.FormulaR1C1 = "=R[-1]C + 0.1"
Range("C3").Select
Selection.copy
Range(nTheRange).Select
ActiveSheet.Paste
End Sub