+ Reply to Thread
Results 1 to 13 of 13

Macro problem - auto fill to last active cell

  1. 10-18-2012, 12:24 PM #1
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Question Macro problem - auto fill to last active cell

    Hello I am relatively new to your forum and was wondering if anyone out there could help me

    I have included my macro below and it is part of a much bigger macro, however at the moment due to the part of code:

    Selection.AutoFill Destination:=Range("A2:B1897")
    &
    Selection.AutoFill Destination:=Range("J2:J1897")

    It seems to not work correctly and if the input report I have is less than the 1897 lines it returns a #n/a.

    What I would like to do within this same macro is only do the steps to the last active cell in the sheet e.g. it would mean that any input report of any size would work and no errors returned.

    Can anyone help me out on this one

    Thanks

    CL
    Last edited by CraigieL; 10-18-2012 at 04:41 PM.
    Reply With QuoteRegister To Reply
  2. 10-18-2012, 12:24 PM #2
    JieJenn
    JieJenn is offline
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Macro help needed

    Can you post a sample file?
    Learn Data Analysis or Excel youtube.com/jiejenn
    Reply With QuoteRegister To Reply
  3. 10-18-2012, 12:28 PM #3
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro help needed

    Hi

    MARCO P1

    Please Login or Register  to view this content.
    Last edited by arlu1201; 10-18-2012 at 01:23 PM.
    Reply With QuoteRegister To Reply
  4. 10-18-2012, 12:28 PM #4
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro help needed

    macro p2

    Please Login or Register  to view this content.
    Last edited by arlu1201; 10-18-2012 at 01:23 PM.
    Reply With QuoteRegister To Reply
  5. 10-18-2012, 12:29 PM #5
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro help needed

    Sorry Could only get on this way at moment
    Reply With QuoteRegister To Reply
  6. 10-18-2012, 01:24 PM #6
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro help needed

    Welcome to the forum.

    I have added code tags to your posts. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.

    Also - you do not need all these rows - 
    Please Login or Register  to view this content.
    etc. You can delete them. They only make your code long and bulky to read and troubleshoot.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  7. 10-18-2012, 04:52 PM #7
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro problem - auto fill to last active cell

    Thank you - I will remove however how would the two problem codes allow for me to auto fill to last active cell- in the codes at the moment only allow a fill to 1897 however I need to allow a fill in both columns on any size of sheet so to the last active cell

    I hope that made sence
    Reply With QuoteRegister To Reply
  8. 10-19-2012, 09:32 AM #8
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro problem - auto fill to last active cell

    Your macro has a lot of extra statements which are not necessary. Can you list out exactly what you want the macro to do (step by step) and we can help you make your macro efficient as well as autofill till the last active cell.
    Reply With QuoteRegister To Reply
  9. 10-19-2012, 10:45 AM #9
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro problem - auto fill to last active cell

    Good Afternoon arlu1201

    The macro is going to be used to carry out many front end functions of a report, the autofill option is to allow for the copy and fill down of a formula and then copy and paste as values.

    It seems to work fine at the moment the only problem I have is that it only seems to fill to line 1897, and I ultimately need to fill on any length of base report, this is why I thought the autofill to last active cell would be best.

    Thanks
    Reply With QuoteRegister To Reply
  10. 10-19-2012, 11:01 AM #10
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro problem - auto fill to last active cell

    Ok. The macro may be working fine, i just wanted to help you make it more efficient because there are lots of unwanted rows of code which if deleted can make your macro run much faster.
    Reply With QuoteRegister To Reply
  11. 10-19-2012, 11:09 AM #11
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro problem - auto fill to last active cell

    Hi arlu1201

    Sorry I did not mean to come across in an adversarial way, I am at a novice stage of VBA, I appreciate the advice to remove these additional lines, Just wondering what I need to add to the following to ensure it fills to the last active cell .

    Please Login or Register  to view this content.
    Where B1897 would be the autofill to last active cell in Column A - Sorry if I seem to be ungrateful at any point that is not the way I mean to portray myself
    Reply With QuoteRegister To Reply
  12. 10-19-2012, 11:13 AM #12
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro problem - auto fill to last active cell

    Hey sure no problem.

    When you are free, we can work together on bettering the code.

    Regarding the above snippet, it can be re-written as follows - 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  13. 10-22-2012, 04:43 AM #13
    CraigieL
    CraigieL is offline
    Registered User
    Join Date
    10-18-2012
    Location
    Scotland
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Macro problem - auto fill to last active cell

    Hi arlu1201

    Thank you for this - I will input this to my code this week and let you know how it goes - thanks you for your help
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1