No of rows as Range

[zeeshah]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>