• Status: Solved
• Priority: Medium
• Security: Public
• Views: 289

# Amend existing macro to retain filters in Excel worksheets

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
gregfthompson
• 15
• 9
1 Solution

Commented:
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

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
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
``````
Regards,
Brian.2011-Australia-ailments-macro-V3.xlsm

However, I have
0

Author Commented:
Thanks Brian,

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

Can you doublecheck?

Thanks,

Greg
0

Author Commented:
Here's a screenshot of the error window.
Compile-Error.jpg
0

Commented:
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 Commented:
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

Commented:
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 Commented:
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

Commented:
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.

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 Commented:
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

Commented:
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 Commented:
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 Commented:
0

Author Commented:
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!!!

Thanks again.

Greg
0

Author Commented:
Thanks heaps!!!
0

Commented:
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 Commented:
Thanks Brian,

Your help is very much appreciated.

Greg
0

Author Commented:
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

Commented:
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 Commented:
0

Author Commented:
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

Commented:
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
``````
0

Author Commented:
Thanks Brian.

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

How do I do this?

Thanks again,

Greg
0

Author Commented:
Thanks Brian.

Found 'module"

All good.

Thanks heaps!!!

Greg
0

Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.