Last Row Variable
 

Below is a snippet of code from a project I'm working on. I import Data and
then perform various functions. The result are used in a Pivot Table. Row 3
will always be the first row containing data, but depending on the import
the ending point, Row 19 in this case, will change. Is there a way to
declare the last Row. It might be easier to declare the last non-empty row
variable as a R1C1 formula. This row number calculates 4 separate formulas.
I have only listed two below.

Thanks for any help,
Joel

'This Calculates Average Manpower
Range("I3").Formula = "=AVERAGE(C3,E3)/$I$1"
Range("I3").Autofill Destination:=Range("I3:I19")
'This Converts Week Beginning Dates to Week Ending Dates
Range("G3").Formula = "=B3+6"
Range("G3").Autofill Destination:=Range("G3:G19")

Last Row Variable
 

cLastRow = Cells(Rows.Count,"C").End(xlUp).Row
'This Calculates Average Manpower
Range("I3").Formula = "=AVERAGE(C3,E3)/$I$1"
Range("I3").Autofill Destination:=Range("I3:I" & cLastRow)
'This Converts Week Beginning Dates to Week Ending Dates
Range("G3").Formula = "=B3+6"
Range("G3").Autofill Destination:=Range("G3:G" & cLastRow)



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joel Mills" wrote in message
...
Below is a snippet of code from a project I'm working on. I import Data
and
then perform various functions. The result are used in a Pivot Table. Row
3
will always be the first row containing data, but depending on the import
the ending point, Row 19 in this case, will change. Is there a way to
declare the last Row. It might be easier to declare the last non-empty
row
variable as a R1C1 formula. This row number calculates 4 separate
formulas.
I have only listed two below.

Thanks for any help,
Joel

'This Calculates Average Manpower
Range("I3").Formula = "=AVERAGE(C3,E3)/$I$1"
Range("I3").Autofill Destination:=Range("I3:I19")
'This Converts Week Beginning Dates to Week Ending Dates
Range("G3").Formula = "=B3+6"
Range("G3").Autofill Destination:=Range("G3:G19")

Last Row Variable
 

Bob,

Thanks for the reply. I used your modification and it almost does what I
want. I'm new at VBA but stumbled on the answer thanks to you. I used
cLastRow = Cells(Rows.Count,"C").End(xlUp).Row -1


"Bob Phillips" wrote in message
...

cLastRow = Cells(Rows.Count,"C").End(xlUp).Row
'This Calculates Average Manpower
Range("I3").Formula = "=AVERAGE(C3,E3)/$I$1"
Range("I3").Autofill Destination:=Range("I3:I" & cLastRow)
'This Converts Week Beginning Dates to Week Ending Dates
Range("G3").Formula = "=B3+6"
Range("G3").Autofill Destination:=Range("G3:G" & cLastRow)



--

HTH

RP
(remove nothere from the email address if mailing direct)


"Joel Mills" wrote in message
...
Below is a snippet of code from a project I'm working on. I import Data
and
then perform various functions. The result are used in a Pivot Table.
Row
3
will always be the first row containing data, but depending on the
import
the ending point, Row 19 in this case, will change. Is there a way to
declare the last Row. It might be easier to declare the last non-empty
row
variable as a R1C1 formula. This row number calculates 4 separate
formulas.
I have only listed two below.

Thanks for any help,
Joel

'This Calculates Average Manpower
Range("I3").Formula = "=AVERAGE(C3,E3)/$I$1"
Range("I3").Autofill Destination:=Range("I3:I19")
'This Converts Week Beginning Dates to Week Ending Dates
Range("G3").Formula = "=B3+6"
Range("G3").Autofill Destination:=Range("G3:G19")