Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula down
Hi,
I need help with answering the below - And that means you would go down one row further that the row with the last used value in that important column. (If you didn't start in row 1, then the ...resize() portion would be different.) I didn't start at row 1 so do not know what I need to change in the below - Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A2").AutoFill .Range("A2").Resize(Row, 1) .Range("F2").AutoFill .Range("F2").Resize(Row, 1) .Range("L2").AutoFill .Range("L2").Resize(Row, 1) .Range("Y2:AA2").AutoFill .Range("Y2:AA2").Resize(Row, 1) .Range("AD2:AG2").AutoFill .Range("AD2:AG2").Resize(Row, 1) .Range("AJ2").AutoFill .Range("AJ2").Resize(Row, 1) .Range("AQ2").AutoFill .Range("AQ2").Resize(Row, 1) End With -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula down
Why start a new thread?
These lines have typos: .Range("A2").AutoFill .Range("A2").Resize(Row, 1) You want to refer to lastrow: .Range("A2").AutoFill .Range("A2").Resize(LastRow, 1) And since you're not starting in row 1, then you want to resize by one fewer rows: .Range("A2").AutoFill .Range("A2").Resize(lastRow - 1, 1) In general, it'll be .Resize(lastrowRow - startingrowgoeshere + 1, 1) or .Range("A2").AutoFill .Range("A2").Resize(lastRow - 2 + 1, 1) in your case. Leanne M (Aussie) wrote: Hi, I need help with answering the below - And that means you would go down one row further that the row with the last used value in that important column. (If you didn't start in row 1, then the ..resize() portion would be different.) I didn't start at row 1 so do not know what I need to change in the below - Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A2").AutoFill .Range("A2").Resize(Row, 1) .Range("F2").AutoFill .Range("F2").Resize(Row, 1) .Range("L2").AutoFill .Range("L2").Resize(Row, 1) .Range("Y2:AA2").AutoFill .Range("Y2:AA2").Resize(Row, 1) .Range("AD2:AG2").AutoFill .Range("AD2:AG2").Resize(Row, 1) .Range("AJ2").AutoFill .Range("AJ2").Resize(Row, 1) .Range("AQ2").AutoFill .Range("AQ2").Resize(Row, 1) End With -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy formula down
Hi Dave,
Sorry for the new thread but in the past my older post's dont get picked up after a while - especially if I have said my question was answered. As it turns out I posted this and then saw your reply to my original one so it was too late then. Thanks for the correcting to my line - I really did not know what to change it to, only knew that I had to change it. Thanks -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) "Dave Peterson" wrote: Why start a new thread? These lines have typos: .Range("A2").AutoFill .Range("A2").Resize(Row, 1) You want to refer to lastrow: .Range("A2").AutoFill .Range("A2").Resize(LastRow, 1) And since you're not starting in row 1, then you want to resize by one fewer rows: .Range("A2").AutoFill .Range("A2").Resize(lastRow - 1, 1) In general, it'll be .Resize(lastrowRow - startingrowgoeshere + 1, 1) or .Range("A2").AutoFill .Range("A2").Resize(lastRow - 2 + 1, 1) in your case. Leanne M (Aussie) wrote: Hi, I need help with answering the below - And that means you would go down one row further that the row with the last used value in that important column. (If you didn't start in row 1, then the ..resize() portion would be different.) I didn't start at row 1 so do not know what I need to change in the below - Dim LastRow As Long With Worksheets("List") 'change IV to the column that can be used to find that last row LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row 'just repeat this for each set of columns that has formulas 'it can be a single column or multiple columns. .Range("A2").AutoFill .Range("A2").Resize(Row, 1) .Range("F2").AutoFill .Range("F2").Resize(Row, 1) .Range("L2").AutoFill .Range("L2").Resize(Row, 1) .Range("Y2:AA2").AutoFill .Range("Y2:AA2").Resize(Row, 1) .Range("AD2:AG2").AutoFill .Range("AD2:AG2").Resize(Row, 1) .Range("AJ2").AutoFill .Range("AJ2").Resize(Row, 1) .Range("AQ2").AutoFill .Range("AQ2").Resize(Row, 1) End With -- Leanne M (Aussie) (Changed Display name from Leanne) United Kingdom (Don''t ask me what an Aussie is doing living in the UK!) -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy formula result (text) only - without copying formula | Excel Discussion (Misc queries) | |||
I copy a formula and the results copy from the original cell | Excel Discussion (Misc queries) | |||
copy formula down a column and have cell references change within formula | New Users to Excel | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
Copy formula so destination displays formula as text | Excel Discussion (Misc queries) |