Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Code for Autofill

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Each day I work with a file containing sales data (the number of rows varies by day). Column A has sales info, Column B contains a formula I need to use. I double click the fill handle to copy down the formula. That's easy enough but is there a way to code this to take into account a varying number of rows?

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi duplinguy,

    Try this macro.

    Sub FillColB()
    'Fills down column B to accommodate new data added to the end of column A
    Dim rngFill As Range
    Set rngFill = Range([b65536].End(xlUp), [a65536].End(xlUp).Offset(0, 1))
    [b65536].End(xlUp).AutoFill rngFill
    End Sub


    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    Board Regular
    Join Date
    Apr 2002
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks that works great!
    Here's another twist to my question.
    Some of my sales data does not need a calculation. So I sort and separate what does and does not need a formula with a few blank rows. The data that doesn't is below the data that does. I can't delete any rows (it's needed for future reference).
    Is there a way to make your macro stop when it encounters the blank rows separating the data?

  4. #4
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again Duplinguy,

    Yes, the code I provided before looks from the last row up to find the last row with data. Here is code that looks from the first row down. This stops when it encounters a row without data. It starts on the second row assuming that the first row contains headers or might be blank.


    Sub FillColB()
    'Fills down column B to accommodate new data added to the end of column A
    Dim rngFill As Range
    Set rngFill = Range([b2].End(xlDown), [a2].End(xlDown).Offset(0, 1))
    [b2].End(xlDown).AutoFill rngFill
    End Sub



    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Posts
    95
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Help! It doesn't seem to be working.
    Sales data is in Column A starting on row 2. The formula I want to autofill is in cell B2.
    By the way, the screen flickers like something may be happening.

  6. #6
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again duplinguy,

    The code I provided works fine in my workbook. I'm wondering if you have some stray data somewhere in columns A or B. How about checking these columns for such stray data by putting your cursor at the end of data in each of these columns and doing a Ctrl-down arrow. If there is no stray data below that point Excel should immediately select A65536 or B65536, depending on column A or B. If it stops on some other cell, this is undoubtedly the problem. Do a clear contents on all such cells.

    Damon

Some videos you may like

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
  •