Counting rows in Excel spreadsheet.

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

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

--
Regards,
Tom Ogilvy




"Jim15" wrote:

>
> 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
>
>
> --
> Jim15
> ------------------------------------------------------------------------
> Jim15's Profile: http://www.excelforum.com/member.php...o&userid=26300
> View this thread: http://www.excelforum.com/showthread...hreadid=547911
>
>

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

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

--
Regards,
Tom Ogilvy

"Jim15" wrote:

>
> 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
>
>
> --
> Jim15
> ------------------------------------------------------------------------
> Jim15's Profile: http://www.excelforum.com/member.php...o&userid=26300
> View this thread: http://www.excelforum.com/showthread...hreadid=547911
>
>

Thanks Mr. Ogilvy!