+ Reply to Thread
Results 1 to 5 of 5

Excel crashes while deleting custom list

Hybrid View

  1. #1
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Excel crashes while deleting custom list

    Hi
    I need to multilevel sort my data. Buttons on my userform from top left need to be clicked in the counting sequence . The six buttons are working fine if I save my wb after applying the functionality of above six buttons then no error but when I click the last button "EmailFlagCustomSort" and then save the wb ,Excel crashes.Problem is solved if I uncomment the red bold line of code.but I cannot execute that line because it will clear the the sorting of above 6 buttons while I want to preserve that sorting and then apply the last button custom sorting. Now the other workaround is that I should comment out the green line (which is actually deleting my custom list after performing the sorting. Reason I want to delete the custom list is that it does not allow me to copy the email flag down (it will replicate the custom list which I don't want).

    Any help would be greatly appreciated . I am struggling the situation for the last 8 hours.SAMPLE WB IS ATTACHED

    Public Sub EmailFlagCustomSort()
    Application.DeleteCustomList Application.CustomListCount + 1
    Dim DataValues As Worksheet
    Dim RngDataValues As Range
    Dim Contacts As Worksheet
    Dim RngContacts
    Dim LstRow As Long
    Dim N As Integer
    Dim arr As Variant
    Application.EnableEvents = False
    Set DataValues = ThisWorkbook.Sheets("DataValues")
    LstRow = DataValues.Range("BO" & Rows.Count).End(xlUp).Row
    Set RngDataValues = DataValues.Range("BO2:BO" & LstRow)
    Set Contacts = ThisWorkbook.Sheets("Contacts")
    Set RngContacts = Contacts.Range("J2:J" & Contacts.Range("J" & Rows.Count).End(xlUp).Row)
    arr = Application.Transpose(RngDataValues)
    'MsgBox UBound(arr)
    'On Error Resume Next
    '    ActiveSheet.Sort.SortFields.Clear
        Application.AddCustomList ListArray:=arr
        N = Application.GetCustomListNum(arr)
        Contacts.Range("A2:Z" & Contacts.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=Range("J2"), Order1:=xlAscending, _
                                Header:=xlNo, Orientation:=xlSortColumns, _
                                OrderCustom:=N + 1
        Erase arr
    '***********************
    'ActiveSheet.Sort.SortFields.Clear
    ' Application.EnableEvents = True
    Application.DeleteCustomList N
    '**********************
    End Sub
    Best Regards
    Imran Bhatti
    Attached Files Attached Files
    Last edited by ImranBhatti; 11-16-2017 at 11:49 AM. Reason: Wrong attachment
    Teach me Excel VBA

  2. #2
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Excel crashes while deleting custom list

    You could simply avoid adding the custom list at all
    Public Sub EmailFlagCustomSort()
    Dim DataValues As Worksheet
    Dim RngDataValues As Range
    Dim Contacts As Worksheet
    Dim RngContacts
    Dim LstRow As Long
    Dim N As Integer
    Dim arr As Variant
    Application.EnableEvents = False
    Set DataValues = ThisWorkbook.Sheets("DataValues")
    LstRow = DataValues.Range("BO" & Rows.Count).End(xlUp).Row
    Set RngDataValues = DataValues.Range("BO2:BO" & LstRow)
    Set Contacts = ThisWorkbook.Sheets("Contacts")
    Set RngContacts = Contacts.Range("J2:J" & Contacts.Range("J" & Rows.Count).End(xlUp).Row)
    arr = Application.Transpose(RngDataValues)
    'MsgBox UBound(arr)
    'On Error Resume Next
    '    ActiveSheet.Sort.SortFields.Clear
        With Contacts.Sort
            With .SortFields
                .Clear
                .Add Key:=Contacts.Range("J2"), Order:=xlAscending, CustomOrder:=Join(arr, ",")
            End With
            .SetRange Contacts.Range("A2:Z" & Contacts.Range("A" & Rows.Count).End(xlUp).Row)
            .Header = xlNo
            .Orientation = xlSortColumns
            .Apply
        End With
        Erase arr
    '***********************
    'ActiveSheet.Sort.SortFields.Clear
    ' Application.EnableEvents = True
    '**********************
    End Sub
    Don
    Please remember to mark your thread 'Solved' when appropriate.

  3. #3
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Excel crashes while deleting custom list

    Hi xlnitwit thanks for the kind review

    Just to be responsive I inform you that currently I am cross checking your solution against the other sorting buttons will take some while. I will let you know the results.
    Last edited by ImranBhatti; 11-16-2017 at 12:09 PM.

  4. #4
    Valued Forum Contributor ImranBhatti's Avatar
    Join Date
    03-27-2014
    Location
    Rawalpindi,Pakistan
    MS-Off Ver
    Office 365
    Posts
    1,784

    Re: Excel crashes while deleting custom list

    Hello again
    Sorry for the delay. Its raining here and there was a temporary electricity failure. The solution worked like a charm. Thank so very much for your time to review the problem.Never thought of that sorting on the basis of a list without creating a custom list
    Again a bundle of thanks xlnitwit.

    Best Regards
    Imran Bhatti

  5. #5
    Forum Guru xlnitwit's Avatar
    Join Date
    06-27-2016
    Location
    London
    MS-Off Ver
    Windows: 2010; Mac: 16.13 (O365)
    Posts
    7,085

    Re: Excel crashes while deleting custom list

    You're welcome, and thank you for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 4
    Last Post: 08-04-2017, 03:02 PM
  2. [SOLVED] Excel Crashes when using locals window to view custom-defined class
    By joe31623 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 02-11-2016, 04:23 PM
  3. Excel crashes / stops working properly after deleting a worksheet
    By Pedsdude in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-02-2014, 05:11 AM
  4. Excel crashes after deleting a sheet and saving
    By Gilli in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-20-2013, 06:21 AM
  5. Replies: 13
    Last Post: 07-15-2013, 08:14 PM
  6. [SOLVED] Deleting duplicate entries in an Excel list
    By ticephotos in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-03-2005, 04:06 PM
  7. Deleting duplicate entries in Excel list
    By ticephotos in forum Excel General
    Replies: 2
    Last Post: 05-03-2005, 02:06 PM

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