+ Reply to Thread
Results 1 to 4 of 4

Error: "Argument is not an option" on Macro

Hybrid View

Luminary Xion Error: "Argument is not an... 02-03-2009, 10:52 PM
Leith Ross Re: Error: "Argument is not... 02-04-2009, 12:45 AM
shg Re: Error: "Argument is not... 02-04-2009, 12:46 AM
Luminary Xion Re: Error: "Argument is not... 02-05-2009, 08:53 PM
  1. #1
    Registered User
    Join Date
    02-19-2008
    Posts
    52

    Error: "Argument is not an option" on Macro

    This Thread describes a function that I've found incredibly useful to reset the "last used cell" in a worksheet.

    I use this function a lot, and I would like to make a macro out of it. I have a menu-item added in my excel that runs the macro;

    Public Sub Reset_lastcell(wS As Worksheet)
        ActiveSheet.UsedRange
        wS.UsedRange
    End Sub
    But attempting to run it gives me the error "Argument is not an option".

    Just copying and paste the code into the "view code" for a worksheet will reset the "last used cell" when a spreadsheet is openned. See above mentioned thread for more detailed descriptions of the problem this solves.

    Please advise on how I can a. fix the menu-item I've created to make it solve the problem described in the above linked thread, or b. help me come up with another option to use (repeatedly, so hopefully easily) on multiple workbooks with this problem.

    Thanks!
    My Last Try At This
    Last edited by Luminary Xion; 02-05-2009 at 08:53 PM.

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Error: "Argument is not an option" on Macro

    Hello Luminary Xion,

    The sub expects you to pass a worksheet to it as an argument.

    example
      Reset_lastcell Sheets(1)
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689

    Re: Error: "Argument is not an option" on Macro

    The sub as written requires an argument. What's your question? If you want to run it without one, then
    Public Sub Reset_lastcell()
        ActiveSheet.UsedRange
    End Sub
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Registered User
    Join Date
    02-19-2008
    Posts
    52

    Re: Error: "Argument is not an option" on Macro

    Solution posed by coworker worked-

    Sub Reset_lastcell()
        For Each wS In ActiveWorkbook.Worksheets
            wS.UsedRange
        Next
    End Sub
    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1