Solved

Amend existing macro to retain filters in Excel worksheets

Posted on 2013-01-16
24
239 Views
Last Modified: 2013-01-23
Macro extracts rows as required but removes filters. Is it possible to amend the macro to retain the filters? File attached.
2011-Australia-ailments-macro.xlsm
0
Comment
Question by:gregfthompson
  • 15
  • 9
24 Comments
 
LVL 26

Expert Comment

by:redmondb
ID: 38785472
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

Open in new window

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

However, I have
0
 

Author Comment

by:gregfthompson
ID: 38785763
Thanks Brian,

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

Can you doublecheck?

Thanks,

Greg
0
 

Author Comment

by:gregfthompson
ID: 38785773
Here's a screenshot of the error window.
Compile-Error.jpg
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38785808
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.
0
 

Author Comment

by:gregfthompson
ID: 38785878
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38785902
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.
0
 

Author Comment

by:gregfthompson
ID: 38785987
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38786062
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.
0
 

Author Comment

by:gregfthompson
ID: 38786657
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
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38786755
Greg,

Sorry, I don't understand what you mean by "All filters are complete". Did you try the two approaches I mentioned? Please see below for screen captures of the second procedure - if you're not getting the same results as I am, please your post screen captures here....(Thanks for the offer of the file, but I'm not eveni thinking of a 90MB file until we can get filters sorted out,)

Regards,
Brian.
0
 

Author Comment

by:gregfthompson
ID: 38790279
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
0
 

Author Comment

by:gregfthompson
ID: 38790280
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:gregfthompson
ID: 38790340
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
0
 

Author Closing Comment

by:gregfthompson
ID: 38790342
Thanks heaps!!!
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38796468
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.
0
 

Author Comment

by:gregfthompson
ID: 38797301
Thanks Brian,

Your help is very much appreciated.

Greg
0
 

Author Comment

by:gregfthompson
ID: 38799454
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
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38799664
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.
0
 

Author Comment

by:gregfthompson
ID: 38800112
0
 

Author Comment

by:gregfthompson
ID: 38800118
Hi Brian,

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

Thanks,

Greg
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38801021
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

Open in new window

0
 

Author Comment

by:gregfthompson
ID: 38808491
Thanks Brian.

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

How do I do this?

Thanks again,

Greg
0
 

Author Comment

by:gregfthompson
ID: 38808617
Thanks Brian.

Found 'module"

All good.

Thanks heaps!!!

Greg
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38809438
Glad to help, Greg!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now