Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill formula down to last data row
Hello All,
I have following code: Dim Lastrow As Long Lastrow = ActiveSheet.Cells(Rows.Count, "A").End (xlUp).Row Worksheets("TEST1").Select Range("O1").Select ActiveCell.FormulaR1C1 = "=OFFSET(RC[-14],0,R1C16)" Range("O1:O" & Lastrow).Filldown Worksheets("TEST2").Select Range("O1").Select ActiveCell.FormulaR1C1 = "=OFFSET(RC[-14],0,R1C16)" Range("O1:O" & Lastrow).Filldown The first sheet does seem to work but the second sheet it fills down only to the 8 row, which in the first sheet that's how far data goes. So I need to fill the formula down to last data row of each sheet. Please advise with any help. thank you, Juan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill formula down to last data row
Juan
When switching to the second sheet you need to recalculate your Lastrow variable i.e repeat the line: Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row Rowan "juan" wrote: Hello All, I have following code: Dim Lastrow As Long Lastrow = ActiveSheet.Cells(Rows.Count, "A").End (xlUp).Row Worksheets("TEST1").Select Range("O1").Select ActiveCell.FormulaR1C1 = "=OFFSET(RC[-14],0,R1C16)" Range("O1:O" & Lastrow).Filldown Worksheets("TEST2").Select Range("O1").Select ActiveCell.FormulaR1C1 = "=OFFSET(RC[-14],0,R1C16)" Range("O1:O" & Lastrow).Filldown The first sheet does seem to work but the second sheet it fills down only to the 8 row, which in the first sheet that's how far data goes. So I need to fill the formula down to last data row of each sheet. Please advise with any help. thank you, Juan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill formula down to last data row
Hello Rowan,
thanks for the info. Works now. Juan -----Original Message----- Juan When switching to the second sheet you need to recalculate your Lastrow variable i.e repeat the line: Lastrow = ActiveSheet.Cells(Rows.Count, "A").End (xlUp).Row Rowan "juan" wrote: Hello All, I have following code: Dim Lastrow As Long Lastrow = ActiveSheet.Cells(Rows.Count, "A").End (xlUp).Row Worksheets("TEST1").Select Range("O1").Select ActiveCell.FormulaR1C1 = "=OFFSET(RC[-14],0,R1C16)" Range("O1:O" & Lastrow).Filldown Worksheets("TEST2").Select Range("O1").Select ActiveCell.FormulaR1C1 = "=OFFSET(RC[-14],0,R1C16)" Range("O1:O" & Lastrow).Filldown The first sheet does seem to work but the second sheet it fills down only to the 8 row, which in the first sheet that's how far data goes. So I need to fill the formula down to last data row of each sheet. Please advise with any help. thank you, Juan . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter data and fill only filtered data | Excel Discussion (Misc queries) | |||
Using Data Fill function to copy a Countif formula | Excel Worksheet Functions | |||
Using Data Fill function to copy a Countif formula | Excel Worksheet Functions | |||
Fill formulas into adjacent cells does not take formula only data | Excel Worksheet Functions | |||
I have a list of data, fill in the gaps. FILL function won't work | Excel Discussion (Misc queries) |