Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilling a formula to a variable last cell
Can someone please help me with the code for the following situation:
I have an excel worksheet that can vary in size by BOTH rows and columns. I need to code an autofill of a formula from cell Q2 (the starting point is always Q2) to the last cell. The Cells that I am autofilling into are empty but the range is framed by Row 1 and Column A which contain data out to the farthest point of that range in each. Thanks, Brian Murphy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilling a formula to a variable last cell
Range("q2").AutoFill Destination:=Range("q2:q" ActiveSheet.UsedRange.Rows.Count), Type:=xlFillDefaul ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilling a formula to a variable last cell
mudraker wrote in message ...
Range("q2").AutoFill Destination:=Range("q2:q" & ActiveSheet.UsedRange.Rows.Count), Type:=xlFillDefault OK, this solution is on the right track but it only autofills down to the last used row in the Q column (which is always the 1st for me). I also need this to autofill across the columns to the last column, which is also variable so its address cannot be hard coded. In the end it should autofill all the cells between Q2 and the last used cell in the worksheet. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilling a formula to a variable last cell
How about:
Option Explicit Sub testme() Dim LastRow As Long Dim LastCol As Long Dim rng As Range With ActiveSheet Set rng = .UsedRange 'try to reset lastused cell With rng LastRow = .Rows(.Rows.Count).Row LastCol = .Columns(.Columns.Count).Column End With .Range("q2").AutoFill _ Destination:=.Range("q2:q" & LastRow), Type:=xlFillDefault .Range("q2:q" & LastRow).AutoFill _ Destination:=.Range("q2", .Cells(LastRow, LastCol)), _ Type:=xlFillDefault End With End Sub Brian Murphy wrote: mudraker wrote in message ... Range("q2").AutoFill Destination:=Range("q2:q" & ActiveSheet.UsedRange.Rows.Count), Type:=xlFillDefault OK, this solution is on the right track but it only autofills down to the last used row in the Q column (which is always the 1st for me). I also need this to autofill across the columns to the last column, which is also variable so its address cannot be hard coded. In the end it should autofill all the cells between Q2 and the last used cell in the worksheet. -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilling a formula to a variable last cell
Dave I lost track of this thread so i am not sure if you got your problem solved. If not - the only way I know of solving your problem is to do it in 2 steps step 1 auto fill rows as per my previos suggestion then auto fill columns dim Row dim Col Row = ActiveSheet.UsedRange.Rows.Count Col = ActiveSheet.UsedRange.Columns.Count Range("q2").AutoFill Destination:=Range("q2:q" & Row), Type:=xlFillDefault Range("q2:q" & Row).AutoFill Destination:=Range("q2:" & Col & row), Type:=xlFillDefault ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofilling a formula to a variable last cell
I think this will cause trouble:
Range("q2:q" & Row).AutoFill Destination:=Range("q2:" & Col & row), Type:=xlFillDefault "Q2:" & Col & row will look like: "Q2:334000" if the last column was 33 and the last row was 4000. (You didn't like my solution to the original poster's question? (I wasn't the OP.)) mudraker wrote: Dave I lost track of this thread so i am not sure if you got your problem solved. If not - the only way I know of solving your problem is to do it in 2 steps step 1 auto fill rows as per my previos suggestion then auto fill columns dim Row dim Col Row = ActiveSheet.UsedRange.Rows.Count Col = ActiveSheet.UsedRange.Columns.Count Range("q2").AutoFill Destination:=Range("q2:q" & Row), Type:=xlFillDefault Range("q2:q" & Row).AutoFill Destination:=Range("q2:" & Col & row), Type:=xlFillDefault ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofilling Sheet Name in Formula | Excel Discussion (Misc queries) | |||
formula referencing a column - autofilling in a row ??? | Excel Discussion (Misc queries) | |||
Autofilling using INDIRECT in a formula | Excel Discussion (Misc queries) | |||
Autofilling incremental numbers in a formula | Excel Worksheet Functions | |||
Autofilling a formula: not working? | Excel Discussion (Misc queries) |