1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel 2013 Sort Macro errors out at .Apply but not in 2010

Discussion in 'Business Applications' started by mariaa33, May 28, 2014.

Thread Status:
Not open for further replies.
Advertisement
  1. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Here is part of my code where it errors out in 2013 for the sort .apply but not 2010 and can't figure out why. Help appreciated. I have recorded same in 2013 with same results.

    Code:
        Application.DisplayAlerts = False
        Workbooks.Open Filename:="C:\Temp Data\mydata.csv"
        Rows("1:1").Select
        Selection.Delete Shift:=xlUp
        Rows("1:1").Select
        Selection.AutoFilter
        ActiveSheet.Range("$A:$AM").AutoFilter Field:=33, Criteria1:="<>"
        Cells.Select
        Selection.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        Range("A1").Select
        ActiveSheet.Paste
        Sheets("Mobility").Select
        ActiveSheet.Range("$A:$AM").AutoFilter Field:=33, Criteria1:="="
        Application.CutCopyMode = False
        Selection.Copy
        Sheets.Add After:=Sheets(Sheets.Count)
        Range("A1").Select
        ActiveSheet.Paste
        Range("AC2").Select
        Application.CutCopyMode = False
        Sheets("Sheet2").Select
        Cells.Select
        Range("U1").Activate
        ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range( _
            "Q:Q"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
            xlSortNormal
        With ActiveWorkbook.Worksheets("Sheet2").Sort
            .SetRange Range("A:AM")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply  <<[COLOR=red]error with Excel 2013 Run-time Error 1004: app-defined or object defined error[/COLOR]>>
        End With
    
     
  2. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    Hi Maria,
    I notice that you explicitly name Sheet2, is it really Sheet2?
    If you add a sheet it during the same session it might not be Sheet2 so I suggest you give that a try.
    If this doesn't work then could you please attach the csv file you're using?
    This will make if much easier to test.
    Make sure that there is private / sensitive data in it.
     
  3. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164

    Unfortunately the data is sensitive and I am not allowed to share and would be pretty impossible to make up. I have another macro that uses .Apply and doesn't error. I also named the sheet2 something specific and tried it and it did the same thing. I even recorded the same steps in 2013 and when I run it, it still errors at the same place. It is the the third worksheet but it auto named itself as Sheet2. I have tried replacing Sheet2 with just (3) but it gives the same exact error.
     
  4. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    Hi, could you post a csv file with the same layout with 5 rows of ficticious data?
     
  5. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Here is a sample file and I verified it is doing the same thing. I changed it to txt since this site doesn't allow .csv files. Thanks
     

    Attached Files:

  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    Got it, will give it a shot and let you know
     
  7. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    I did the following, nothing fancy:
    I copied the code you posted in a Excel file named 'maria33-Test.xlsm'
    I only changed the filepath since I use another drive and folder

    The resulting file I saved as xlsx file 'Mobility.xlsx'

    It ran without an error in my Excel 2010 version.

    I've attached both files, if you want to run it you'll have to change the folder reference to the csv file.

    I noticed that the macro was very slow and that's because you select $A:$AM which in my opinion is overkill since you're selecting over a million orws (!), I suggest you limit this to the number of rows used instead, but I'm sure you can figure that one out.
     

    Attached Files:

  8. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Hans, I have never had any issues running it in 2010...That was what I said in my original post...It was only when I tried to run the same exact code in 2013

    "Here is part of my code where it errors out in 2013 for the sort .apply but not 2010 and can't figure out why."
     
  9. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    Sorry, my mistake.:eek:
    I'll give it a test om my office 2013 netbook and let you know
     
  10. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    As I suspected.
    The fact that the all the rows are selected forses the error.
    When sorting it seems that Excel 2013 needs the some extra space to carry it out.

    If you limitthe code when sorting to $A1:$AM200 the code runs without a hitch
    I just tested it.
     
  11. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Hans, Thank you for sticking with it. Such a simple error to drive me batty. I changed it to use ActiveSheet.UsedRange and it worked great! Thank you for helping me!
     
  12. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,592
    First Name:
    Hans
    That's what we try to do here at TechGuy, I joined to learn and try to help with what I learn and learned.
    Glad it worked :)
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1126828

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice