Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No of rows as Range
Hi, I need to write the macro that calculate a function that should be fil all the rows with particular row. i include the coding. here the Range will be Range("G3:nRows") the nRows is the No. of rows with used cells(with contents) How to calculate the nRows?? code:: Sub CalculateTime() ' ' CalculateTime Macro ' Macro recorded 3/2/2006 by Shah ' Dim nRows As Long nRows = Application.ActiveSheet.UsedRange.Rows.Count ' Range("G3").Select 'nRows = ActiveSheet.UsedRange.Rows.Count 'nRows = G2000 ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],R[-1]C+150,61366)" Selection.AutoFill Destination:=Range("G3:nRows") Type:=xlFillDefault ' Selection.AutoFill Destination:=Range("G3:G17750") Type:=xlFillDefault ' Cells.Value 'Range("G3:G28884").Select Range("G2").Select End Su -- zeesha ----------------------------------------------------------------------- zeeshah's Profile: http://www.excelforum.com/member.php...fo&userid=2286 View this thread: http://www.excelforum.com/showthread.php?threadid=51813 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
No of rows as Range
Sub CalculateTime()
' CalculateTime Macro ' Macro recorded 3/2/2006 by Shah Dim nRows As Long nRows = Application.ActiveSheet.UsedRange.Rows.Count ' Range("G3").Select 'nRows = ActiveSheet.UsedRange.Rows.Count 'nRows = G2000 ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],R[-1]C+150,61366)" Selection.AutoFill Destination:=Range("G3:G" & nRows), _ Type:=xlFillDefault ' Selection.AutoFill Destination:=Range("G3:G17750"), ' Type:=xlFillDefault ' Cells.Value 'Range("G3:G28884").Select Range("G2").Select End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "zeeshah" wrote in message ... Hi, I need to write the macro that calculate a function that should be fill all the rows with particular row. i include the coding. here the Range will be Range("G3:nRows") the nRows is the No. of rows with used cells(with contents) How to calculate the nRows?? code:: Sub CalculateTime() ' ' CalculateTime Macro ' Macro recorded 3/2/2006 by Shah ' Dim nRows As Long nRows = Application.ActiveSheet.UsedRange.Rows.Count ' Range("G3").Select 'nRows = ActiveSheet.UsedRange.Rows.Count 'nRows = G2000 ActiveCell.FormulaR1C1 = "=IF(RC[-4]=R[-1]C[-4],R[-1]C+150,61366)" Selection.AutoFill Destination:=Range("G3:nRows"), Type:=xlFillDefault ' Selection.AutoFill Destination:=Range("G3:G17750"), Type:=xlFillDefault ' Cells.Value 'Range("G3:G28884").Select Range("G2").Select End Sub -- zeeshah ------------------------------------------------------------------------ zeeshah's Profile: http://www.excelforum.com/member.php...o&userid=22867 View this thread: http://www.excelforum.com/showthread...hreadid=518138 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can change range to select active rows instead of :=Range("S10 | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
Used Rows Range? | Excel Programming | |||
Rows in a Range | Excel Programming | |||
group rows in a range based on criteria from another range (vba) | Excel Programming |