CODEWITHSUNDEEP
excelexcel-2007
Jeremy F.
Jeremy F.

Reputation: 1878

Excel 2007: Refresh data connection and Show/Hide updated filter results

Using Excel 2007, I have a data connection that automatically refreshes upon opening. Within my table, the last column is called 'Show/Hide' and is set to 1(show) or 0 (hide).

After the data connection is finished updating the data and some of the 'Show/Hide' values have changed, I still have to manually show everything in the 'Show/Hide' column, then in the filter I have select '1' to show the correct data.

How do I get the 'show/hide' column to automatically refresh when the data refreshes?

Before (showing 2 items):

Color   Is Color   Show/Hide
Red        Y          1
Blue       Y          1
Widget     N          0

After (showing 3 items after manually adjusting filter):

Color   Is Color   Show/Hide
Red        Y          1
Blue       Y          1
Widget     N          0
Black      Y          1

Upvotes: 1

Views: 2968

Answers (1)

Justin Ohms
Justin Ohms

Reputation: 3543

Your best bet will be to use a macro. Either attach it to startup or another trigger like a button.

If you know how to write macros you would use the VB command "ActiveWorkbook.RefreshAll" followed by the a refresh of your filter "AutoFilter.ApplyFilter" (This is a very basic example, your filter may have a name etc.)

If you don't know VB you can record the macro and perform the operations you want then just play it back. That should work just as well.

Upvotes: 2

Related Questions