Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill in VBA question
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill in VBA question
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" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill in VBA question
"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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill in VBA question
one way:
Sub test() Dim lRow As Long lRow = ActiveSheet.UsedRange.SpecialCells(xlCellTypeLastC ell).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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofill in VBA question
Thanks Ron, You are a Prince.
It works perfectly. "Ron de Bruin" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofill question | Excel Programming | |||
Autofill question | Excel Worksheet Functions | |||
Q. Autofill question: Can I autofill alpha characters like I can numbers? | Excel Programming | |||
AutoFill Question | Excel Programming | |||
Autofill question | Excel Programming |