Writing a Macro to complete Autofill functions

  • Hi there,


    I would be really grateful if anybody can help with this problem.


    I've put together several workbooks that have a set of formula in specific cells. I then input a set of independent variables in one work book, and because the other workbook is linked, I can then go to the 2nd workbook and simply click on a selected row and drag one row down to complete a series of calculations.


    All I'm wanting to acheive is to able to write a Macro that completes the autofill function so I don't have to click and drag every time. When I use the Macro recorder, it writes the Macro in such a way that the Macro completes the autofill function for a specific designated cell. However it won't update and I don't know what code to use to get the Macro to increment to the following row.....


    So the main query is: Is there a piece of code that I can input into the Macro so that the Macro increments an extra row everytime it is used?


    I've got a spreadsheet attached that represents what I'm talking about and a simple Macro in it.


    Cheers.


    Scott.

  • Re: Writing a Macro to complete Autofill functions


    Well to find the last row of data in a column you can use something like this.

    Code
    LastRow = Range("C65536").End(xlUp).Row


    This could then be used like this.

    Code
    Range("C10:I" & LastRow)
  • Re: Writing a Macro to complete Autofill functions


    Hi Nori,


    Thanks for the information, and also for responding so quickly. I am now revealing my TOTAL lack of knowledge of Macros/VBA here when I write the next statement.....I don't quite understand where to employ your suggested lines in the existing Macro, DOH!


    At present the Macro looks something like this:


    Sub Macro1()
    '
    ' Macro1 Macro
    ' Macro recorded 20/05/2005 by Scott Bruce
    '
    ' Keyboard Shortcut: Ctrl+t
    '
    Sheets("Calculations").Select
    Range("C10:I10").Select
    Selection.AutoFill Destination:=Range("C10:I11"), Type:=xlFillDefault
    Range("C10:I11").Select
    End Sub



    I tried putting the pieces of code that you suggested in place but then I'm running into compiling errors. I've got a strong suspicion that what you've suggested is 100% right, but I don't quite understand where to put the code.


    For example when the first line of the Macro [Range("C10:I10").Select] is to select these certain designated cells, should I be putting this line [LastRow = Range("C65536").End(xlUp).Row] in its place instead?


    What are your thoughts?


    Thanks again,


    Scott.



    Quote from norie

    Well to find the last row of data in a column you can use something like this.

    Code
    LastRow = Range("C65536").End(xlUp).Row


    This could then be used like this.

    Code
    Range("C10:I" & LastRow)
  • Re: Writing a Macro to complete Autofill functions


    This worked for me with out being in the Activesheet.


  • Re: Writing a Macro to complete Autofill functions


    Hi Guys,


    Thanks for the code! I really appreciate the help. I think it would have taken me 10 years to come up with that solution at the rate I was progressing! Going forward I think this will help me understand and learn more about VBA.



    Thanks again for the help.


    Scott.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!