Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: autofill problem in macro

    Hello,

    I ran through a macro and it does what I want...sort of. In 3 separate places in this macro I autofilled a formula by double clicking to get it to the last row....figuring it would do what I did instead of save the actual place. Instead, every time I run this macro, it autofills to 107,xxx and that's not what I want. How do I manipulate this to do what I want?

    Here is a sample of the code:

    Code:
    Selection.AutoFill Destination:=Range("R2:R107394")
        Range("R2:R107394").Select
        ActiveWindow.ScrollRow = 149
        ActiveWindow.ScrollRow = 593
        ActiveWindow.ScrollRow = 2219
        ActiveWindow.ScrollRow = 4881
        ActiveWindow.ScrollRow = 10057
        ActiveWindow.ScrollRow = 18782
        ActiveWindow.ScrollRow = 27360
        ActiveWindow.ScrollRow = 38303
        ActiveWindow.ScrollRow = 47029
        ActiveWindow.ScrollRow = 54571
        ActiveWindow.ScrollRow = 61226
        ActiveWindow.ScrollRow = 64627
        ActiveWindow.ScrollRow = 68324
        ActiveWindow.ScrollRow = 70247
        ActiveWindow.ScrollRow = 71873
        ActiveWindow.ScrollRow = 73204
        ActiveWindow.ScrollRow = 72909
        ActiveWindow.ScrollRow = 73204
        ActiveWindow.ScrollRow = 73500
        ActiveWindow.ScrollRow = 73648
        ActiveWindow.ScrollRow = 74240
        ActiveWindow.ScrollRow = 76014
        ActiveWindow.ScrollRow = 78085
        ActiveWindow.ScrollRow = 80894
        ActiveWindow.ScrollRow = 84296
        ActiveWindow.ScrollRow = 86958
        ActiveWindow.ScrollRow = 89472
        ActiveWindow.ScrollRow = 91838
        ActiveWindow.ScrollRow = 93908
        ActiveWindow.ScrollRow = 95683
        ActiveWindow.ScrollRow = 97753
        ActiveWindow.ScrollRow = 98937
        ActiveWindow.ScrollRow = 100267
        ActiveWindow.ScrollRow = 101155
        ActiveWindow.ScrollRow = 102190
        ActiveWindow.ScrollRow = 102782
        ActiveWindow.ScrollRow = 103077
        ActiveWindow.ScrollRow = 103373
        ActiveWindow.ScrollRow = 103669
        ActiveWindow.ScrollRow = 104113
        ActiveWindow.ScrollRow = 104408
        ActiveWindow.ScrollRow = 104852
        ActiveWindow.ScrollRow = 105296
        ActiveWindow.ScrollRow = 105443
        ActiveWindow.ScrollRow = 105591
        ActiveWindow.ScrollRow = 105739
        ActiveWindow.ScrollRow = 106035
        ActiveWindow.ScrollRow = 106183
        ActiveWindow.ScrollRow = 106331
        ActiveWindow.ScrollRow = 106479
        ActiveWindow.ScrollRow = 107070
        ActiveWindow.ScrollRow = 107218
        ActiveWindow.ScrollRow = 107366
        Columns("R:R").ColumnWidth = 11.29
        ActiveWindow.ScrollRow = 107070
        ActiveWindow.ScrollRow = 106035
        ActiveWindow.ScrollRow = 104408
        ActiveWindow.ScrollRow = 102338
        ActiveWindow.ScrollRow = 100711
        ActiveWindow.ScrollRow = 99380
        ActiveWindow.ScrollRow = 98197
        ActiveWindow.ScrollRow = 96275
        ActiveWindow.ScrollRow = 93317
        ActiveWindow.ScrollRow = 88141
        ActiveWindow.ScrollRow = 83704
        ActiveWindow.ScrollRow = 80894
        ActiveWindow.ScrollRow = 80747
        ActiveWindow.ScrollRow = 80599
        ActiveWindow.ScrollRow = 80451
        ActiveWindow.ScrollRow = 80303
        ActiveWindow.ScrollRow = 80155
        ActiveWindow.ScrollRow = 80007
        ActiveWindow.ScrollRow = 79859
        ActiveWindow.ScrollRow = 79711
        ActiveWindow.ScrollRow = 79563
        ActiveWindow.ScrollRow = 78972
        ActiveWindow.ScrollRow = 77345
        ActiveWindow.ScrollRow = 74979
        ActiveWindow.ScrollRow = 70542
        ActiveWindow.ScrollRow = 63888
        ActiveWindow.ScrollRow = 59155
        ActiveWindow.ScrollRow = 52944
        ActiveWindow.ScrollRow = 46289
        ActiveWindow.ScrollRow = 37860
        ActiveWindow.ScrollRow = 31501
        ActiveWindow.ScrollRow = 24846
        ActiveWindow.ScrollRow = 20853
        ActiveWindow.ScrollRow = 18191
        ActiveWindow.ScrollRow = 16268
        ActiveWindow.ScrollRow = 13902
        ActiveWindow.ScrollRow = 12128
        ActiveWindow.ScrollRow = 10353
        ActiveWindow.ScrollRow = 8726
        ActiveWindow.ScrollRow = 6508
        ActiveWindow.ScrollRow = 5177
        ActiveWindow.ScrollRow = 3698
        ActiveWindow.ScrollRow = 2515
        ActiveWindow.ScrollRow = 1332
        ActiveWindow.ScrollRow = 149
        ActiveWindow.ScrollRow = 1

  2. #2
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    First of all we can tidy it up a little to make it clearer what's going on.

    You don't need all of those ActiveWindow.ScrollRow calls which are recordings of you scrolling up and down the window. Once they're removed, your code becomes:
    Code:
        Selection.AutoFill Destination:=Range("R2:R107394")
        Range("R2:R107394").Select
        Columns("R:R").ColumnWidth = 11.29

    In 3 separate places in this macro I autofilled a formula by double clicking to get it to the last row....figuring it would do what I did instead of save the actual place. Instead, every time I run this macro, it autofills to 107,xxx and that's not what I want. How do I manipulate this to do what I want?
    You'll have to add some logic to tell it to fill to the "last row". When you say "last row", do you mean the last row on the worksheet, or do you mean the last used row in the worksheet?

    Hope that helps..

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by Colin Legg View Post
    Hi,

    First of all we can tidy it up a little to make it clearer what's going on.

    You don't need all of those ActiveWindow.ScrollRow calls which are recordings of you scrolling up and down the window. Once they're removed, your code becomes:
    Code:
        Selection.AutoFill Destination:=Range("R2:R107394")
        Range("R2:R107394").Select
        Columns("R:R").ColumnWidth = 11.29


    You'll have to add some logic to tell it to fill to the "last row". When you say "last row", do you mean the last row on the worksheet, or do you mean the last used row in the worksheet?

    Hope that helps..
    That gave me a good starting point and I was able to get the rest working as desired.

    However, I discovered that if my button for the macro is over a cell that gets autofilled, it also autofills the button......Why.........?

    How do I keep that from happening, other than just simply moving the button?

  4. #4
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Chris,

    Please can you zip and attach an example workbook for me to see?

  5. #5
    Join Date
    Oct 2009
    Posts
    204
    Quote Originally Posted by Colin Legg View Post
    Hi Chris,

    Please can you zip and attach an example workbook for me to see?
    Here it is. I copy all from the .xlsx and paste into .xlsm, then click the button. It's just if I have the button over one of the columns that gets autofilled, like d-f or q-r, it also autofills the button...seems weird to me. The way it is now, it works fine.
    Attached Files Attached Files

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi Chris,

    Thanks for the attachment; I understand the problem now.

    If you right click on the button and choose Format control, navigate to the Properties Tab and choose "Don't move or size with cells" in the object positioning section, then you should find that the button will not be copied and filled down the column.

    Hope that helps...

  7. #7
    Join Date
    Oct 2009
    Posts
    204
    Yes, that got it.

    Thank you!

Posting Permissions

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