Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- Jim15 ------------------------------------------------------------------------ Jim15's Profile: http://www.excelforum.com/member.php...o&userid=26300 View this thread: http://www.excelforum.com/showthread...hreadid=547911 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting rows in Excel spreadsheet.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting rows in Excel spreadsheet.
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting rows in Excel spreadsheet.
Thanks Mr. Ogilvy! -- Jim15 ------------------------------------------------------------------------ Jim15's Profile: http://www.excelforum.com/member.php...o&userid=26300 View this thread: http://www.excelforum.com/showthread...hreadid=547911 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting rows in a spreadsheet, but I don't want to count hidden r | Excel Discussion (Misc queries) | |||
Excel 2003 function for counting rows with two conditions. | Excel Worksheet Functions | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) | |||
Counting Rows Then Counting Values in Columns | Excel Programming | |||
cells() and counting number or rows on spreadsheet | Excel Worksheet Functions |