Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
variable height variable width stacked bar charts | Charts and Charting in Excel | |||
Sum cells based on a row variable and seperate column variable | Excel Worksheet Functions | |||
why is it saying sheetcnt is "variable not defined" how to do a global variable to share over multiple functions in vba for excel? | Excel Worksheet Functions | |||
Cells.Find error Object variable or With block variable not set | Excel Programming | |||
Pivot Table - Object variable or with block variable not set? | Excel Programming |