• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

VBA Filter If Criteria is Matched Filter If not Select Blank|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / VBA Filter If Criteria is Matched Filter If not Select Blank|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosVBA Filter If Criteria is Matched F…
sp_PrintTopic sp_TopicIcon
VBA Filter If Criteria is Matched Filter If not Select Blank
Avatar
Richard Louie
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 18, 2020
sp_UserOfflineSmall Offline
1
September 18, 2020 - 8:58 am
sp_Permalink sp_Print

I would like a VBA macro so that if I wanted to see only a specific number (e.g. 100) filtered then have the filter in Pivot Table data must filtered to "100", otherwise filtered to “Blank”. If the 100 is not found in the Data then select “Blank”.

Example

Data

100

101

102

103

104

Blank

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
September 18, 2020 - 9:43 am
sp_Permalink sp_Print

Hi Richard,

There's not enough info here for me to understand what you are trying to do.

If you want to filter a pivot table to only show 100 then you can use the PT filters?  Why use VBA?

Please supply a workbook with data that can be used to illustrate your situation and desired results.

Regards

Phil

Avatar
Richard Louie
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 18, 2020
sp_UserOfflineSmall Offline
3
September 18, 2020 - 10:39 am
sp_Permalink sp_Print

Hello Philip - Please see attached sample. I put some comments into the file.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
4
September 18, 2020 - 10:44 am
sp_Permalink sp_Print sp_EditHistory

Hi Richard,

no file attached.

A video showing how to attach a file can be found here https://www.myonlinetraininghu.....and-guides

Regards

Phil

Avatar
Richard Louie
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 18, 2020
sp_UserOfflineSmall Offline
5
September 18, 2020 - 1:37 pm
sp_Permalink sp_Print

Hello Philip hopefully its attached now.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
6
September 18, 2020 - 2:26 pm
sp_Permalink sp_Print

Thanks Richard.  So what you are saying is that if you have a company '200' that isn't in the source data, you want the pivot table to set the filter to 'blank' because '200' isn't in your source?

Where are you getting the value '200' from?  You haven't explained the full picture and I can't write any code without knowing details.  Where would the code get the values from to use as PT filters?

Regards

Phil

Avatar
Richard Louie
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 18, 2020
sp_UserOfflineSmall Offline
7
September 18, 2020 - 2:36 pm
sp_Permalink sp_Print

Hello Philip

Yes I want to set the filter in the Pivot Table to 'blank' because '200' isn't in your source data. 

The 200 is just an example. Basically wanted to show a data table and have an individual pivot for each company.

Data Tab has the two data fields: Column A = Company Number, Column B = Sales Data.

I then want a few Pivot Tables lets say for Company 100 through 200 in separate pivots. If it finds the match to the company codes that is set/hard coded then use the company code number as the filter. If there is no match to the assigned company code for each pivot then select the blank (e.g. 106  to 200 would be blank for the pivot filter since they are not in the Data set).

Sorry if I'm not explaining everything clearly.

Richard

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
8
September 18, 2020 - 4:13 pm
sp_Permalink sp_Print sp_EditHistory

Hi Richard,

I'm still unsure as to where the values for the filter are coming from, specifically for companies that aren't in your source data. 

I can write some code that will look through your source data and change the PT filter for every company it finds, but I don't see how or why it would set the filter to a company that isn't in the source data.  Why do you want this to happen?  Where would the VBA code get the company values from? If it's from the source data then why would the code try to filter for a company that doesn't exist?

Perhaps all you need is 'Show Report Filter Pages...' ?

Click into one of your pivot tables, then from the Ribbon on the PivotTable Analyze tab (I'm using Office 365) click the drop down arrow beside 'Options' in the PivotTable group (the left-most area of the Ribbon) - see attached image.

Then click on 'Show Report Filter Pages...' and from the pop-up window click OK. 

This creates a sheet with a PT for every company in the source data.

Regards

Phil

srptf.pngImage Enlarger

sp_PlupAttachments Attachments
  • sp_PlupImage srptf.png (9 KB)
Avatar
Richard Louie
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 18, 2020
sp_UserOfflineSmall Offline
9
September 18, 2020 - 9:32 pm
sp_Permalink sp_Print

Hi Matt,

Appreciate all the help and time you have given.

The "Data" can change month to month but I want a specific Pivot to populate only if a certain company code is found, in this case 200.

This is the VBA code that I was using to set the Pivot data to filter on company code 200:

ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").ClearAllFilters
ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").CurrentPage = _
"200"

Since the Data set didn't contain any company code 200 data the filter defaulted to "ALL". Is there VBA logic to say is company code 200 is not found the pivot would select the Blank instead?

Does this make more sense what I'm asking?

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
10
September 18, 2020 - 10:57 pm
sp_Permalink sp_Print sp_EditHistory

Hi Richard,

You can trap the error that is generated when you try to set the filter to a value that isn't in the source data.  When this occurs you then set the filter to (blank)

ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").ClearAllFilters
On Error Resume Next
ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").CurrentPage = "200"
If Err.Number > 0 Then
ActiveSheet.PivotTables("TBPivotTable").PivotFields("Company").CurrentPage = "(blank)"
End If
On Error GoTo 0

Regards

Phil

P.S. Matt?

Avatar
Richard Louie
Member
Members
Level 0
Forum Posts: 6
Member Since:
September 18, 2020
sp_UserOfflineSmall Offline
11
September 19, 2020 - 5:11 am
sp_Permalink sp_Print

THANK YOU SO MUCH THIS IS PERFECT!

Really appreciate all the time and help Philip.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
12
September 19, 2020 - 7:42 am
sp_Permalink sp_Print

no worries

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Ahmad Alkhuffash, Chandler Davis, Ramon Lagos, Paul Pritchard, michael serna
Guest(s) 11
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Sopi Yuniarti
LAFONSO HERNANDEZ
Hayden Hao
Angela chen
Sean Moore
John Chisholm
Forum Stats:
Groups: 3
Forums: 24
Topics: 6215
Posts: 27245

 

Member Stats:
Guest Posters: 49
Members: 31897
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.