VBA to FillDown with Offset() from ActiveCell

  • Thread starter Thread starter Dennis
  • Start date Start date
D

Dennis

XL 2003

OK I give up! Need help.

Trying to Fill Down with an Offset from the Selected cell.

I cannot get the correct VBA code.

My last attempt which bombed was:

ActiveCell.Offset(1, 1).AutoFill Destination:=Range( ActiveCell.Offset(1, 1) & _
Range("A" & Rows.Count).End(xlUp).Row), Type:=xlFillCopy

How do I get an Offset range variable into the Range Object to properly use this
function?

Can Offset somehow use the R1C1 notation?

TIA Dennis.

(I know that I will say how did I miss the solution - it was soooo obvious)
 
Try something like this Dennis

Sub test()
Dim Lrow As Long
Lrow = Range("A" & Rows.Count).End(xlUp).Row
With ActiveCell.Offset(1, 1)
..AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Lrow, .Column))
End With
End Sub
 
Ron, Thanks!

It works fine.

Was I on the wrong path attempting to insert Offset() function into the formula?

If you do not have the time I understand.

Dennis
 
Hi Dennis

The range needs a Start cell and a End cell

Your code give only the start cell and the last number of col A
ActiveCell.Offset(1, 1) & last number of col A)
This is not a correct range

You can change your example also to this
See that I use cells to make the End cell

ActiveCell.Offset(1, 1).AutoFill Destination:=Range(ActiveCell.Offset(1, 1), _
Cells(Range("A" & Rows.Count).End(xlUp).Row, ActiveCell.Offset(1, 1).Column)), Type:=xlFillCopy
 
Back
Top