Amend existing macro to retain filters in Excel worksheets

Hi, gregfthompson.

The attached saves the sheets' filter data using Custom Views, runs your existing code and then restores the Custom View. The code is...

Sub kTest()
    
    Dim i As Long, j As Long, r As Range, MyCodes
    Dim c As Long, Fmla  As String, p   As Long
    
    MyCodes = Array(2621)   '<<< put the required codes here
    
    Const HeaderRow As Long = 3
    
    Application.ScreenUpdating = False

    On Error Resume Next
        ActiveWorkbook.CustomViews("$Temp$").Delete
    On Error GoTo 0
    ThisWorkbook.CustomViews.Add ViewName:="$Temp$", PrintSettings:=True, RowColSettings:=True
    
    For i = 1 To Worksheets.Count
        With Worksheets(i)
                        
            If .AutoFilterMode Then .AutoFilterMode = False
            
            p = .Range("a" & .Rows.Count).End(3).Row
            c = .Cells(HeaderRow, .Columns.Count).End(-4159).Column + 2
            
            With .Range("a" & HeaderRow & ":a" & p)
                Fmla = "=AND(A" & HeaderRow + 1 & "<>{"
                For j = LBound(MyCodes) To UBound(MyCodes)
                    Fmla = Fmla & MyCodes(j) & ","
                Next
                Fmla = Left(Fmla, Len(Fmla) - 1) & "})"
                .Cells(2, c).Formula = Fmla
                .AdvancedFilter 1, .Cells(1, c).Resize(2)
                Set r = .Cells(1).Offset(1).Resize(.Rows.Count - 1).SpecialCells(12)
                If Not r Is Nothing Then r.EntireRow.Delete
                Set r = Nothing
                .Cells(2, c).ClearContents
            End With
            
            .ShowAllData
                                    
        End With
    Next
    
    ThisWorkbook.CustomViews("$Temp$").Show
    ActiveWorkbook.CustomViews("$Temp$").Delete

End Sub

Select allOpen in new window

Regards,
Brian.2011-Australia-ailments-macro-V3.xlsm

However, I have

Thanks Brian,

There seems to be a problem. When I ran it a window opened"Compile Error".

Can you doublecheck?

Thanks,

Greg

Here's a screenshot of the error window.
Compile-Error.jpg

Greg,

From the screen shot I can see that the file is not the one I posted! My latest file is "2011-Australia-ailments-macro-V3.xlsm" whereas the one you show has a completely different name.

I can see at least some of my code there, so it looks like you've had some copy and pasting mishap. Please run the macro in V3 without making any changes - you won't see that error. (I even downloaded it again, to be sure!)

Regards,
Brian.

Thanks Brian,

I downloaded and ran the attached as suggested. It does work with a small problem. In some of the worksheets the extracted rows are hidden and do not unhide.

At the same time the file on which the macro is required is 54,000 rows (too big for experts-exchange) and when I copy the macro to this file the "Compile Error: Expected: end of statement" window appears.

Anything you can do to help will be very much appreciated.

Thanks,

Greg

Greg,

I downloaded and ran the attached as suggested. It does work with a small problem. In some of the worksheets the extracted rows are hidden and do not unhide.
Which sheet(s)? It dropped the filters for all of them for me - I checked!

At the same time the file on which the macro is required is 54,000 rows
Do a "Save As" and select "Excel Binary Workbook (*.xlsb)" as the "Save as type:". How big is the new xlsb file? (EE's limit is 50MB.)

Edit:
54,000 rows
Is that per sheet or in total? I deleted all the sheets except "1 Arthritis". In that sheet, I duplicated the data until there were 96,000 rows. This saved to an xlsm as 11mb, and to an xlsb as 5.6mb. (14 sheets each with 54,000 rows is 50.7MB.)

Thanks,
Brian.

Thanks Brian,

The macro works to extract each row where the postcode is 2621 in the attached file. There should be 13 data rows in each worksheet.

In the attached example file where the macro worked (mostly), worksheet 2 shows data in rows 4 to 15, rows 16 and 17 are hidden and I am unable to unhide them.
In worksheet 3, data is shown in row 9, all other rows containing data are hidden.
In worksheet 5, row 17 is hidden and will not unhide.
In worksheet 6, all rows except row 5 are hidden and will not unhide.
In worksheet 7, all data rows are hidden and will not unhide.
In worksheet 9, data is shown only in rows 4 and 16, all other rows are hidden and will not unhide.
In worksheet 11, data rows 4,5,6,10,11,16,17 are shown. All others are hidden and will not unhide.
In worksheet 12, data rows 4 and17 are shown. All others are hidden and will not unhide.
In worksheet 13, data rows 9,10,11 are shown. All others are hidden and will not unhide.
In worksheet 14, no data rows are shown and none will unhide.

The complete file is 97MB. I can drop it into dropbox if you wish. Send me an email or an invitation to gregthompson@bigpond.com

Thanks again,

Greg
2011-Australia-ailments-macro-V3.xlsm

Greg,

I'm simply not seeing the issues you describe. I've checked and responded to each of your comments below. Assuming that you were using the standard unfilter procedures I describe and that they weren't working for you then you've got something corrupt - and neither the original V3 nor your version of it are causing problems on my PC.

Please recycle Windows, start Excel, load your version of V3 and try to unfilter any of the sheets. Don't open any other spreadsheets (don't mind about your Personal.xlsb). What happens?

Oops, I'm using 2010 (and I also tested some of the sheets in 2007). What version are you on?

worksheet 2 shows data in rows 4 to 15, rows 16 and 17 are hidden and I am unable to unhide them.
4/9/10/11/12/15 are visible - which is correct based on filtering column J for "3". Either of the following successfully displays rows 5-8/13-14/16-17
 - On the Ribbon, click on "Sort and Filter" and select "Clear".
 - Click on the on the Filter dropdown for column J and select "Clear Filter from "65-74"".
In worksheet 3, data is shown in row 9, all other rows containing data are hidden.
Correct - Column G is filtered to only display values of "32".
In worksheet 5, row 17 is hidden and will not unhide.
Rows 9 and 17 are hidden - and each displays when I do either of the actions described for sheet 2.
In worksheet 6, all rows except row 5 are hidden and will not unhide.
Display all entries by using one of the procedures described for sheet 2.
In worksheet 7, all data rows are hidden and will not unhide.
Display all entries by using one of the procedures described for sheet 2.
In worksheet 9, data is shown only in rows 4 and 16, all other rows are hidden and will not unhide.
Display all entries by using one of the procedures described for sheet 2.
In worksheet 11, data rows 4,5,6,10,11,16,17 are shown. All others are hidden and will not unhide.
...and row 9. Display all entries by using one of the procedures described for sheet 2.
In worksheet 12, data rows 4 and17 are shown. All others are hidden and will not unhide.
Display all entries by using one of the procedures described for sheet 2.
In worksheet 13, data rows 9,10,11 are shown. All others are hidden and will not unhide.
Display all entries by using one of the procedures described for sheet 2.
In worksheet 14, no data rows are shown and none will unhide.
Just to be difficult, two of the columns are filtered here! Either unfilter each of them or use the "Sort & Filter" procedure.described for sheet 2.

Regards,
Brian.

Thanks Brian.

I really appreciate your effort on this.

In the complete file I've simply highlighted the row and clicked on filter. All filters are complete.

In the original sample all filters are complete.

This suggests something is happening when the macro runs. Or not?

I have no idea.

I'm happy to send you the entire file.

Thanks again,

Greg

Thanks Brian.

I did as you suggested and amended the filters in each worksheet on the V3 file and the missing rows appeared.

The problem is that the filters were cleared in the the original file when it contained about 300 rows before the macro was run. After the macro was run the filters were altered in some worksheets - some areas had been ticked and not others - hiding some rows.

Please check the filters in the original sample file attached where there are no specific selections in any of the the filters.

Thanks so much for your help so far,

Greg

2011-Australia-ailments-macro.xlsx

Brian,

I'm not sure what was happening on my machine but I deleted all versions and started again with your VBA and it all works!!!

Thank you heaps for your excellent code and your incredible patience.

Thanks again.

Greg

Thanks heaps!!!

Thanks, Greg.

I normally keep an eye on "my" closed questions for at least a couple of weeks, so if you get any issues with this please feel free to post here.

Regards,
Brian.

Thanks Brian,

Your help is very much appreciated.

Greg

Brian,

Can you make another amendment to this macro? Or should set it as another question for you?

The request is to have the file save as an .xlsx with the name being the postcode.

Thanks,

Greg

Greg,

No problem. However I'm conscious of the behind-the-scenes changes that were going on, so please post your full latest working code here and I'll amend that. (Take the file, add a new sheet, delete all the other sheets and save the file under a new name.)

Regards,
Brian.

2011-Australia-ailments-sample.xlsm

Hi Brian,

Take the file, add new sheets for each existing sheet, delete all original sheets and save file under new name.

Thanks,

Greg

Greg,

Unless there's a good reason otherwise, best practice is to keep your modules in a Module rather than a Sheet. In addition, keeping kTest in a Sheet means that you'll have to respond to a message every time you run the macro below. So, I recommend that you put both macros in a new Module.

Regards,
Brian.

Sub Split_Workbook_into_Xlsx()
Dim xSheet As Worksheet

For Each xSheet In ThisWorkbook.Worksheets
    
    Application.ScreenUpdating = False

        Sheets(xSheet.Name).Copy
        ActiveWorkbook.SaveAs Filename:=xSheet.Name, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        ActiveWorkbook.Close
    
    Application.ScreenUpdating = True
    
Next

End Sub

Select allOpen in new window

Thanks Brian.

Much appreciated.  I don't know about "module".

How do I do this?

Thanks again,

Greg

Thanks Brian.

Found 'module"

All good.

Thanks heaps!!!

Greg

Glad to help, Greg!