What is the VB code for autofilling relative cells?

[Guest]

I have recorded a macro, and one of my procedures requires using the
autofill feature. Even thought I selected "relative" values when I began
recording, the auto fill used the absolute values. Now when I run the macro
on worksheets with more data, it only autofills up until the row I recorded
the macro with.

In other words, I would like to convert this code:

Selection.AutoFill Destination:=ActiveCell.Range("A1:A5")

to read something like this:

Selection.AutoFill Destination:=ActiveCell.Range("A1:The entire length
the previous column")

Many thanks.

 

[Bernie Deitrick]

StarDust,

To match the column to the left of the activecell:

ActiveCell.AutoFill Destination:=Range(ActiveCell, _
ActiveCell(1, 0).End(xlDown)(1, 2))

HTH,
Bernie
MS Excel MVP

 

[faisca_aem]

Thanks Bernie, the code works great!

(I'm Stardustsparklin, but I've created a different profile in thi
forum)

I was wondering what additional code I would need to select th
autofilled data, copy it and paste it over the previous column. Righ
now it looks like this:


Code
-------------------
ActiveCell.AutoFill Destination:=Range(ActiveCell, _
ActiveCell(1, 0).End(xlDown)(1, 2))
ActiveCell.Range("A1:A5").Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft

-------------------

Obviously, I do not wish to just select A1 through A5. Its a dynami
range and the range will change from time to time.

Ultimately, what I'm doing is converting the values of column A fro
text to number. I start by inserting a column next to it and insertin
this formula *=A1/1*. Then I autofill that cell down, copy the values
paste them over the original values in the previous column and delet
the "temporary" column I had just created. Whew!

Is there an easier way to do this in a macro

 

[faisca_aem]

after further investigation i discovered this:


Code
-------------------
Range(ActiveCell, ActiveCell.End(xlDown)).Selec
-------------------


although my code may not be the most efficient, it seems to work fine.


working code:

Code
-------------------
ActiveCell.Offset(0, 1).Range("A2").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "=RC[-1]/1"
ActiveCell.Select
ActiveCell.AutoFill Destination:=Range(ActiveCell, _
ActiveCell(1, 0).End(xlDown)(1, 2))
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLef