I have 9000 to 25000 rows of data depending on the file and the macro I am
running must sum a column. Currently I sum the column down to over 30000
rows. I want to be able to sum the column in the first blank row of that
cell regardless of the number of rows I have without having to go 30000 rows
every time.

Thanks

Depending whether you want the result to be a hard coded value or a formula...

Sub test()
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = _
Application.Sum(Columns("A"))
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Formula = _
"=Sum(" & Range("B2", Cells(Rows.Count, "B").End(xlUp)).Address & ")"
End Sub

Column A is a value while B is a formula...
--
HTH...

Jim Thomlinson


"rockytopfan4ever" wrote:

I have 9000 to 25000 rows of data depending on the file and the macro I am
running must sum a column. Currently I sum the column down to over 30000
rows. I want to be able to sum the column in the first blank row of that
cell regardless of the number of rows I have without having to go 30000 rows
every time.

Thanks

Range("H8").Select
ActiveCell.FormulaR1C1 = ""
Range("H5").Select
ActiveCell.FormulaR1C1 = "=9/3600/24"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=11/24/3600"
Range("H8").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]R5C8,IF(RC[-1]<R6C8,IF(RC[9]<R[-1]C[9],IF(RC[46]=R2C5,IF(RC[46]<=R3C5,RC[-1],0),0),0),0),0)"
Range("H8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("H8:H30000").Select
Selection.FillDown
Range("H30001").Select
Selection.NumberFormat = "[h]:mm:ss;@"

This is a small section of the code in my macro how should i go about
replacing my
Range("H8:H30000").Select with the code you provided?

"Jim Thomlinson" wrote:

Depending whether you want the result to be a hard coded value or a formula...

Sub test()
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = _
Application.Sum(Columns("A"))
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Formula = _
"=Sum(" & Range("B2", Cells(Rows.Count, "B").End(xlUp)).Address & ")"
End Sub

Column A is a value while B is a formula...
--
HTH...

Jim Thomlinson


"rockytopfan4ever" wrote:

I have 9000 to 25000 rows of data depending on the file and the macro I am
running must sum a column. Currently I sum the column down to over 30000
rows. I want to be able to sum the column in the first blank row of that
cell regardless of the number of rows I have without having to go 30000 rows
every time.

Thanks

Try this...

Range("H8").Value = "" 'You can delete this line...
Range("H5").Formula = "=9/3600/24"
Range("H6").Formula = "=11/24/3600"
Range("H8").FormulaR1C1 = _
"=IF(RC[-1]R5C8,IF(RC[-1]<R6C8,IF(RC[9]<R[-1]C[9],IF(RC[46]=R2C5,IF(RC[46]<=R3C5,RC[-1],0),0),0),0),0)"
Range(H8, Range(H8).End(xlDown)).FillDown
with Cells(rows.count, "H").end(xlup).offset(1,0)
.NumberFormat = "[h]:mm:ss;@"
.Formula = _
"=Sum(" & Range("H8", Cells(Rows.Count, "H").End(xlUp)).Address & ")"
End with
--
HTH...

Jim Thomlinson


"rockytopfan4ever" wrote:

Range("H8").Select
ActiveCell.FormulaR1C1 = ""
Range("H5").Select
ActiveCell.FormulaR1C1 = "=9/3600/24"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=11/24/3600"
Range("H8").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]R5C8,IF(RC[-1]<R6C8,IF(RC[9]<R[-1]C[9],IF(RC[46]=R2C5,IF(RC[46]<=R3C5,RC[-1],0),0),0),0),0)"
Range("H8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("H8:H30000").Select
Selection.FillDown
Range("H30001").Select
Selection.NumberFormat = "[h]:mm:ss;@"

This is a small section of the code in my macro how should i go about
replacing my
Range("H8:H30000").Select with the code you provided?

"Jim Thomlinson" wrote:

Depending whether you want the result to be a hard coded value or a formula...

Sub test()
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = _
Application.Sum(Columns("A"))
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Formula = _
"=Sum(" & Range("B2", Cells(Rows.Count, "B").End(xlUp)).Address & ")"
End Sub

Column A is a value while B is a formula...
--
HTH...

Jim Thomlinson


"rockytopfan4ever" wrote:

I have 9000 to 25000 rows of data depending on the file and the macro I am
running must sum a column. Currently I sum the column down to over 30000
rows. I want to be able to sum the column in the first blank row of that
cell regardless of the number of rows I have without having to go 30000 rows
every time.

Thanks

I cant get it to run correctly. Here is my code. Any help will be greatly
appreciated.


Sub TOTALHRS2()
'
' SUPERMACRO
' Macro recorded 08/22/2007 by EngineStand_2
'
' Keyboard Shortcut: Ctrl+t

'KENS

Rows("1:1").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown

ActiveWindow.SmallScroll ToRight:=3
Range("CN7").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-91]<1,0,IF(RC[-78]=R[-1]C[-78],0,IF(AVERAGE(RC[-43]:RC[-42])<R2C5,0,IF(AVERAGE(RC[-43]:RC[-42])R3C5,0,1))))"
Range("CN7").Select
Selection.Copy
Range("CN8:CN30000").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("CN6").Select
ActiveCell.FormulaR1C1 = "CYCLE VALIDATOR"
Range("CN12").Select
Columns("CN:CN").EntireColumn.AutoFit
Columns("CN:CN").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
ActiveWindow.SmallScroll Down:=15

ActiveWindow.SmallScroll Down:=18
Range("CN30002").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-29995]C:R[-1]C)"
Range("CN30002").Select

Range("CA2").Select
ActiveCell.FormulaR1C1 = "Valid Cycles"
Range("CB4").Select
Columns("CA:CA").EntireColumn.AutoFit
Range("CB2").Select
ActiveCell.FormulaR1C1 = "=R[29999]C[12]*10"
Range("CB2").Select

ActiveCell.FormulaR1C1 = "=R[30000]C[12]*10"
Range("CA1").Select
ActiveCell.FormulaR1C1 = "Test HRS Required ?"
Range("CA3").Select
ActiveCell.FormulaR1C1 = "Cycles to be inserted on BLK 240 step 8"
Range("CB5").Select
Columns("CB:CB").EntireColumn.AutoFit
Columns("CA:CA").EntireColumn.AutoFit
Range("CB3").Select
ActiveCell.FormulaR1C1 = ""
Range("CA1:CB3").Select
Selection.Cut

Range("A1").Select
ActiveSheet.Paste
Range("E3").Select
Columns("CA:CA").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Range("B3").Select
ActiveCell.FormulaR1C1 = "=ROUNDUP(IF(R[-2]C<1,""
"",(((R[-2]C*60)*60)-R[-1]C)/60),0)"

Range("B1").Select
ActiveCell.FormulaR1C1 = "50"
Range("B1").Select
Selection.ClearContents
Range("D3").Select
'
' RYAN'S Macro
' Macro recorded 8/30/2007 by turkiws
'

'
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("G:G").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight

Columns("BB:BB").Select
Selection.Insert Shift:=xlToRight

Range("F30001").Select
ActiveCell.FormulaR1C1 = "END"
Range("G30001").Select
ActiveCell.FormulaR1C1 = "End"
Range("H30001").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-29993]C:R[-1]C)"
Range("F30002").Select

Range("BB30001").Select
ActiveCell.FormulaR1C1 = "END"
Range("BB7").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-2]:RC[-1])/2"
Range("BB7").Select
Range(Selection, Selection.End(xlDown)).Select
Range("BB7").Select
Range(Selection, Selection.End(xlDown)).Select
Range("BB7:BB30000").Select
Selection.FillDown

Range("F7").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("F7").Select
Selection.NumberFormat = "m/d/yyyy h:mm:ss"
Columns("F:F").EntireColumn.AutoFit
Range(Selection, Selection.End(xlDown)).Select
Range("F7:F30000").Select
Selection.FillDown

Range("G8").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
Range("G9").Select
Columns("G:G").EntireColumn.AutoFit
Range("G8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("G8:G30000").Select
Range("G9").Activate
ActiveWindow.SmallScroll Down:=21

Range("G8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("G8:G30000").Select
Selection.FillDown

Range("H8").Select
ActiveCell.FormulaR1C1 = ""
Range("H5").Select
ActiveCell.FormulaR1C1 = "=9/3600/24"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=11/24/3600"
Range("H8").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]R5C8,IF(RC[-1]<R6C8,IF(RC[9]<R[-1]C[9],IF(RC[46]=R2C5,IF(RC[46]<=R3C5,RC[-1],0),0),0),0),0)"
Range("H8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("H8:H30000").Select
Selection.FillDown
Range("H30001").Select
Selection.NumberFormat = "[h]:mm:ss;@"

Range("H2").Select
ActiveCell.FormulaR1C1 = "=R[29999]C"
Range("G2").Select
ActiveCell.FormulaR1C1 = "Total Hrs"
Range("G1").Select
Columns("G:G").ColumnWidth = 8.43
Columns("H:H").ColumnWidth = 10.86

'input exhaust temps


Range("d2").Select
ActiveCell.FormulaR1C1 = "Exhaust LSL"
Range("D3").Select
ActiveCell.FormulaR1C1 = "Exhaust USL"
Range("D4").Select
Columns("D:D").EntireColumn.AutoFit

' format text to blue
'


Range("D2:D3,A1").Select
Range("A1").Activate
With Selection.Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 5
End With
Range("F3").Select
Columns("D:D").EntireColumn.AutoFit
Range("B1,E2:E3").Select
Range("E2").Activate
With Selection.Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
Range("F3").Select


'
' format numbers in cells


'

'

Range("E2:E3").Select
Selection.NumberFormat = "General"
Range("H2").Select
Selection.NumberFormat = "[h]:mm:ss;@"
End Sub


Thanks



"Jim Thomlinson" wrote:

Try this...

Range("H8").Value = "" 'You can delete this line...
Range("H5").Formula = "=9/3600/24"
Range("H6").Formula = "=11/24/3600"
Range("H8").FormulaR1C1 = _
"=IF(RC[-1]R5C8,IF(RC[-1]<R6C8,IF(RC[9]<R[-1]C[9],IF(RC[46]=R2C5,IF(RC[46]<=R3C5,RC[-1],0),0),0),0),0)"
Range(H8, Range(H8).End(xlDown)).FillDown
with Cells(rows.count, "H").end(xlup).offset(1,0)
.NumberFormat = "[h]:mm:ss;@"
.Formula = _
"=Sum(" & Range("H8", Cells(Rows.Count, "H").End(xlUp)).Address & ")"
End with
--
HTH...

Jim Thomlinson


"rockytopfan4ever" wrote:

Range("H8").Select
ActiveCell.FormulaR1C1 = ""
Range("H5").Select
ActiveCell.FormulaR1C1 = "=9/3600/24"
Range("H6").Select
ActiveCell.FormulaR1C1 = "=11/24/3600"
Range("H8").Select
ActiveCell.FormulaR1C1 = _

"=IF(RC[-1]R5C8,IF(RC[-1]<R6C8,IF(RC[9]<R[-1]C[9],IF(RC[46]=R2C5,IF(RC[46]<=R3C5,RC[-1],0),0),0),0),0)"
Range("H8").Select
Range(Selection, Selection.End(xlDown)).Select
Range("H8:H30000").Select
Selection.FillDown
Range("H30001").Select
Selection.NumberFormat = "[h]:mm:ss;@"

This is a small section of the code in my macro how should i go about
replacing my
Range("H8:H30000").Select with the code you provided?

"Jim Thomlinson" wrote:

Depending whether you want the result to be a hard coded value or a formula...

Sub test()
Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Value = _
Application.Sum(Columns("A"))
Cells(Rows.Count, "B").End(xlUp).Offset(1, 0).Formula = _
"=Sum(" & Range("B2", Cells(Rows.Count, "B").End(xlUp)).Address & ")"
End Sub

Column A is a value while B is a formula...
--
HTH...

Jim Thomlinson


"rockytopfan4ever" wrote:

I have 9000 to 25000 rows of data depending on the file and the macro I am
running must sum a column. Currently I sum the column down to over 30000
rows. I want to be able to sum the column in the first blank row of that
cell regardless of the number of rows I have without having to go 30000 rows
every time.

Thanks