+ Reply to Thread
Results 1 to 7 of 7

VBA Advanced Filter causing Excel file to corrupt...

  1. #1
    Registered User
    Join Date
    04-24-2013
    Location
    Northampton,England
    MS-Off Ver
    2010
    Posts
    8

    VBA Advanced Filter causing Excel file to corrupt...

    Hi Fellow Excel enthusiasts

    I have a workbook with a worksheet containing a single table of data. I am trying to write VBA to perform an advanced filter and to filter the results from applying the search criteria to another worksheet contained in the same workbook.

    Here is the Code:-
    Please Login or Register  to view this content.
    Option Explicit

    Private Sub Dyno_Range1()
    Dim LastRow As Long
    LastRow = Sheet4.Range("Q" & Rows.Count).End(xlUp).Row
    Sheet4.Range("a3:z" & LastRow).Name = "Data"
    End Sub

    Private Sub Dyno_Range21()
    Dim LastRow As Long
    LastRow = Sheet33.Range("m" & Rows.Count).End(xlUp).Row
    Sheet33.Range("a6:r" & LastRow).Name = "_Depreciation"
    End Sub

    Private Sub Filter_Depreciation()
    '
    ' Advanced Filter on tab depreciation
    '
    On Error GoTo ErrHandler
    Call Dyno_Range1
    Sheets("Depreciation").Range("E17").Select
    Range("Data").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
    "E1:F2"), CopyToRange:=Sheets("Depreciation").Range("A6:r6"), Unique:=False
    Range("k7").Select
    ActiveWorkbook.Worksheets("Depreciation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Depreciation").Sort.SortFields.Add Key:=Range("M7"), _
    SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    Call Dyno_Range21
    Range("_Depreciation").Select
    ActiveWorkbook.Worksheets("Depreciation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Depreciation").Sort.SortFields.Add Key:=Range( _
    "k:k"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("Depreciation").Sort
    .SetRange Range("_Depreciation")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    ActiveWindow.SmallScroll Down:=12
    ActiveSheet.PageSetup.PrintArea = Selection.Address
    ErrHandler:
    On Error Resume Next
    End Sub
    Please Login or Register  to view this content.
    After I run the code and save the workbook down and then open I am getting a warning message that reads "We found a problem with some content in "filename.xlsm". Do you want to try to recover as mush as we can? If you trust the source of this workbook click yes".

    If I click yes the workbook opens. Phew. If I go to the Name Manager and delete the name ranges and then save and close and reopen the issue goes away.

    I think the problem stems with my code for advanced filter when the search has no data values in the main data table.

    I would really value any help/comments to fix this.

    Thanks in advance

    Pablos
    Last edited by Pablos690_1; 02-21-2016 at 11:38 AM. Reason: added workbook

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: VBA Advanced Filter causing Excel file to corrupt...

    What happens if you remove On Error Goto ErrHandler?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    04-24-2013
    Location
    Northampton,England
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA Advanced Filter causing Excel file to corrupt...

    Hi Norie,

    Thanks for the reply.

    Removing the error handling makes no difference in that issue persists

    I thought good vba should have error handling in there...

    More to do with the fact that there are no entries from the advanced filter seems to cause Name range issues?

    The code works ok on another sheet where the advanced filter does return data values...I save...I close..I open...no issues..

    Pablos

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,597

    Re: VBA Advanced Filter causing Excel file to corrupt...

    Pablos

    When you remove On Error... are any other errors/problems highlighted?

    PS Using error handling can be a good idea but it's better to have something a bit more specific than having an all encompassing On Error... statement at the start of the code.

  5. #5
    Forum Expert
    Join Date
    04-01-2013
    Location
    East Auckland
    MS-Off Ver
    Excel 365
    Posts
    1,271

    Re: VBA Advanced Filter causing Excel file to corrupt...

    You should put your code in those code tags
    the # in the tool bar above the window you type into.

    And for what it is worth an example spreadsheet would be even better.

    Just a wild stab in the dark - but its quick to test - do you have a lot of conditional formatting? if so try clearing it and seeing if it still fails.

  6. #6
    Registered User
    Join Date
    04-24-2013
    Location
    Northampton,England
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA Advanced Filter causing Excel file to corrupt...

    Hi Scottiex

    Thanks for feed back. Tried what you have suggested.
    Pablos

  7. #7
    Registered User
    Join Date
    04-24-2013
    Location
    Northampton,England
    MS-Off Ver
    2010
    Posts
    8

    Re: VBA Advanced Filter causing Excel file to corrupt...

    In excel, Name Manager, can you have two 'refers to' that refer to the same range? I wonder whether this is the problem with my VBA? Any thoughts?

    I think I have fixed my VBA. The line of code :-
    Sheet33.Range("a6:r" & LastRow).Name = "_Depreciation"

    I changed to:-
    Sheet33.Range("a6:r" & LastRow + 1).Name = "_Depreciation"

    Still interested to hear your thoughts....
    Last edited by Pablos690_1; 02-20-2016 at 06:43 PM.

+ 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. Run advanced filter after opening the file
    By intexxx in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-17-2014, 10:35 AM
  2. advanced filter - button to re apply advanced filter across multiple sheets
    By motmac87 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-05-2013, 11:16 PM
  3. Corrupt Excel File? <## NASCA DRM FILE - VER1.00 ##>
    By Testrider in forum Excel General
    Replies: 3
    Last Post: 11-29-2011, 06:01 PM
  4. Excel 2007 : Password causing corrupt files
    By JimBobBowie in forum Excel General
    Replies: 0
    Last Post: 10-12-2011, 07:03 AM
  5. Replies: 2
    Last Post: 04-16-2011, 09:29 PM
  6. Excel file merged to WP Data file goes corrupt
    By plynn in forum Excel General
    Replies: 0
    Last Post: 05-30-2007, 11:33 AM
  7. [SOLVED] Corrupt Excel File
    By Hervinder in forum Excel General
    Replies: 1
    Last Post: 06-13-2006, 08:15 AM

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