Counting rows in Excel spreadsheet.

[Jim15]

I recorded the following macro to calculate data in rows. However, the
spreadsheet I was working with had 421 rows. Is there something that
will replace the following lines to only process the number of rows in
a spreadsheet that are present?

Selection.AutoFill Destination:=Range("I2:I421")
Range("I2:I421").Select
Cells.Select


Thanks,

Jim

Sub Prod_Abstract()
'
' Prod_Abstract Macro
' Macro recorded 3/9/2006 by JBW
'
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "MMCFE"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=SUM((RC[-2]*6+RC[-1])/1000)"
Selection.Copy
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("I2:I421")
Range("I2:I421").Select
Cells.Select
Selection.Sort Key1:=Range("I2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Columns("G:I").Select
Selection.NumberFormat = "#,##0"
End Sub

 

[Tom Ogilvy]

Assume we can figure out the number of rows by looking a column A (adjust to
point to the column that will work).

Dim lastrow as Long
lastrow = cells(rows.count,"A").End(xlup).row
Selection.AutoFill Destination:=Range("I2:I" & lastrow)
Range("I2:I" & lastrow).Select

If you want to use

Cells.select
then you don't need
Range("I2:I" & lastrow).Select

 

[Jim15]

Thanks for the solution but the sort at the end of the macro no longer
works.

Here is the code.

Sub Prod_Abstract()
'
' Prod_Abstract Macro
' Macro recorded 3/9/2006 by JBW
'
Dim lastrow As Long
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "MMCFE"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=SUM((RC[-2]*6+RC[-1])/1000)"
Selection.Copy
Application.CutCopyMode = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Selection.AutoFill Destination:=Range("I2:I" & lastrow)
Range("I2:I" & lastrow).Select
Selection.Sort Key1:=Range("I2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal
Columns("G:I").Select
Selection.NumberFormat = "#,##0"
End Sub

 

[Tom Ogilvy]

I said you don't need to do both selects. In this case, you need to use the
cells.select

Sub Prod_Abstract()
'
' Prod_Abstract Macro
' Macro recorded 3/9/2006 by JBW
'
Dim lastrow As Long
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I1").Select
ActiveCell.FormulaR1C1 = "MMCFE"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=SUM((RC[-2]*6+RC[-1])/1000)"
Selection.Copy
Application.CutCopyMode = False
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Selection.AutoFill Destination:=Range("I2:I" & lastrow)
Cells.Select
Selection.Sort Key1:=Range("I2"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Columns("G:I").Select
Selection.NumberFormat = "#,##0"
End Sub

 

[Jim15]

Thanks Mr. Ogilvy!