Autofilling a formula to a variable last cell

  • Thread starter Thread starter Brian Murphy
  • Start date Start date
B

Brian Murphy

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
 
Range("q2").AutoFill Destination:=Range("q2:q"
ActiveSheet.UsedRange.Rows.Count), Type:=xlFillDefaul
 
mudraker said:
Range("q2").AutoFill Destination:=Range("q2:q" &
ActiveSheet.UsedRange.Rows.Count), Type:=xlFillDefault
Click to expand...

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.
 
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
 
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
 
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.))
 
Back
Top