macro with autofill
Find bottlenecks in your Excel workbooks
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: macro with autofill

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    hello all,

    i have recorded multiple macros that make liberal use of autofill within. i reuse the macro weekly. when i edit the macro, the auotfill command within might look something like:

    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B4828")

    my problem is, the size of the file is different each week, so i have always edited these recorded lines to over fill:

    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B6000")

    and this will work until the file grows larger than 6000 lines.

    i want to know how i can fix this to adapt to any size file (rows) that might occur.


    thank you for any help you can offer!


  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You should be able to use some type of shift-ctrl-down language. Try recording a macro and do shift-ctrl-down in it to see what VB language is produced, then try to implement.

  3. #3
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Like tk19 said try using this:

    Range("b2").Select
    NumRows = Range(Selection, Selection.End(xlDown)).Count

    NumRows is the number of used rows in column B starting with cell B2

  4. #4
    New Member
    Join Date
    Mar 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    i can see how the code you supply counts the rows. the physical operation i use when recording the macro is:

    colA has header and data in rows 2 thru 100
    i insert new col to the right
    i place my formula in B2
    i then double click lower right hand corner in B2
    since cells a2:a100 have data, the formula in B2 copies all the way down to b100 then stops.

    when i look at the macro, it then has:

    Selection.AutoFill Destination:=Range("B2:B100")

    so how would i use the count code you supplied to define the range in the autofill code so that next week when there are 200 rows of data it will adapt?

    thanks for the helps gents, i do appreciate!


  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    The following should work for you, if I understand what you want to do:

    Range("a2").Select
    fillrange = Range(Selection, Selection.End(xlDown)).Offset(0, 1).Address
    Set SourceRange = Worksheets("Sheet1").Range("b2")
    SourceRange.AutoFill Destination:=Range(fillrange)

    The code finds the used range in column A and then takes the same range in column B and sets it to the fill range. Then it sets the soure range to the formula in Cell B2. Make sure there is something in B2 or it won't work.

    Hope this helps.
    Kind regards, Al.

  6. #6
    New Member
    Join Date
    Mar 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-15 14:40, muster101 wrote:
    i can see how the code you supply counts the rows. the physical operation i use when recording the macro is:

    colA has header and data in rows 2 thru 100
    i insert new col to the right
    i place my formula in B2
    i then double click lower right hand corner in B2
    since cells a2:a100 have data, the formula in B2 copies all the way down to b100 then stops.

    when i look at the macro, it then has:

    Selection.AutoFill Destination:=Range("B2:B100")

    so how would i use the count code you supplied to define the range in the autofill code so that next week when there are 200 rows of data it will adapt?

    thanks for the helps gents, i do appreciate!

    You can do all of what you want with the following 2 lines of code. The formula in the code is just for illustration.

    Columns(2).Insert
    Range([A2], [A2].End(xlDown)).Offset(0, 1).FormulaR1C1 = "=RC[-1]"

    To get the syntax for your actual formula, select B2:B3 (or any other range of cells in a single column), switch on the macro recorder, type in your formula, press Ctrl+Enter.


    [ This Message was edited by: Anonymuus on 2002-03-15 16:06 ]

  7. #7
    New Member
    Join Date
    Mar 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Just wanted to say THANKS! to all responders.

    Now...if i could just have someone help me with my NCAA office pool...

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com