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

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

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.

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

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

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