I am not a VBA programmer by any stretch of the imagination. I am here to
ask for help with a macro that I _recorded_.

Part of what my macro does is:

Insert a column in a sheet of data
Type a formula into the top cell of the new column
Copy the formula down the column for as far as there is data

I did this last step by double clicking the fill handle and this is the code
that is generated

Range("B4").Select
Selection.AutoFill Destination:=Range("B4:B450")
Range("B4:B450").Select

That's OK this time, but what about when there is more or less lines of
data.

How can this be changed to accomplish what I am looking for?

Thanks,
Carlos

Hi Carlos

Try this

Will fill B1 till the row of the last cell with data in A

Sub test2()
Dim LastRow As Long
With Worksheets("Sheet1")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("B1").AutoFill Destination:=.Range("B1:B" & LastRow) _
, Type:=xlFillDefault
End With
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"CarlosAntenna" <(E-Mail Removed)> wrote in message news:(E-Mail Removed)...
>I am not a VBA programmer by any stretch of the imagination. I am here to
> ask for help with a macro that I _recorded_.
>
> Part of what my macro does is:
>
> Insert a column in a sheet of data
> Type a formula into the top cell of the new column
> Copy the formula down the column for as far as there is data
>
> I did this last step by double clicking the fill handle and this is the code
> that is generated
>
> Range("B4").Select
> Selection.AutoFill Destination:=Range("B4:B450")
> Range("B4:B450").Select
>
> That's OK this time, but what about when there is more or less lines of
> data.
>
> How can this be changed to accomplish what I am looking for?
>
> Thanks,
> Carlos
>
>

"CarlosAntenna" wrote:

> I am not a VBA programmer by any stretch of the imagination. I am here to
> ask for help with a macro that I _recorded_.
>
> Part of what my macro does is:
>
> Insert a column in a sheet of data
> Type a formula into the top cell of the new column
> Copy the formula down the column for as far as there is data
>
> I did this last step by double clicking the fill handle and this is the code
> that is generated
>
> Range("B4").Select
> Selection.AutoFill Destination:=Range("B4:B450")
> Range("B4:B450").Select
>
> That's OK this time, but what about when there is more or less lines of
> data.
>
> How can this be changed to accomplish what I am looking for?
>
> Thanks,
> Carlos
>
>
>

one way:

Sub test()
Dim lRow As Long
lRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastCell).Row
Range("B4:B" & lRow).FillDown
End Sub



--
Hope that helps.

Vergel Adriano


"CarlosAntenna" wrote:

> I am not a VBA programmer by any stretch of the imagination. I am here to
> ask for help with a macro that I _recorded_.
>
> Part of what my macro does is:
>
> Insert a column in a sheet of data
> Type a formula into the top cell of the new column
> Copy the formula down the column for as far as there is data
>
> I did this last step by double clicking the fill handle and this is the code
> that is generated
>
> Range("B4").Select
> Selection.AutoFill Destination:=Range("B4:B450")
> Range("B4:B450").Select
>
> That's OK this time, but what about when there is more or less lines of
> data.
>
> How can this be changed to accomplish what I am looking for?
>
> Thanks,
> Carlos
>
>
>

Thanks Ron, You are a Prince.

It works perfectly.


"Ron de Bruin" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Hi Carlos
>
> Try this
>
> Will fill B1 till the row of the last cell with data in A
>
> Sub test2()
> Dim LastRow As Long
> With Worksheets("Sheet1")
> LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
> .Range("B1").AutoFill Destination:=.Range("B1:B" & LastRow) _
> , Type:=xlFillDefault
> End With
> End Sub
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "CarlosAntenna" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>I am not a VBA programmer by any stretch of the imagination. I am here to
>>ask for help with a macro that I _recorded_.
>>
>> Part of what my macro does is:
>>
>> Insert a column in a sheet of data
>> Type a formula into the top cell of the new column
>> Copy the formula down the column for as far as there is data
>>
>> I did this last step by double clicking the fill handle and this is the
>> code that is generated
>>
>> Range("B4").Select
>> Selection.AutoFill Destination:=Range("B4:B450")
>> Range("B4:B450").Select
>>
>> That's OK this time, but what about when there is more or less lines of
>> data.
>>
>> How can this be changed to accomplish what I am looking for?
>>
>> Thanks,
>> Carlos