Not to filter a table when there is no matching text

bosko2

New Member
Joined
Nov 3, 2020
Messages
22
Office Version
  1. 365
Platform
  1. Windows
Hi all,

I hope this is something that isn't too difficult, and I would also like to thank in advance.

I have a three drop downs (data validation) for a user to select their department in the company; the drop downs reflect a hierarchy (e.g. Level 1 = CEO, Level 2 = General Managers, and Level 3 = Managers)

If the user selects ALL three drop down boxes and hits the "create report" button I've linked to some VBA, they are presented with the table filtered to the three levels they've selected.

The issue is if I am a General Manager (Level 2) and I don't want to select a Level 3 by leaving it blank (essentially the GM wants to see all his Level 3 Mangers data). When the General Manager hits the "create report" button, they are taken to an empty list. I assume this is because there are no "nulls" in the Level 3 column.

I need something in my VBA that says if the relevant drop down is not chosen, blank or null, to ignore it and return ALL values in the Level 3 column.

Any assistance would be greatly appreciated.

Cheers,
Mike
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
What code do you currently have?
 
Upvote 0
See below for the code (I'm not sure if pasting in will work:

VBA Code:
Sub Report_Builder()

Sheets("Actions").Range("A1").AutoFilter Field:=11, _
Criteria1:=Sheets("Report Creator").Cells(2, 1).Value

Sheets("Actions").Range("A1").AutoFilter Field:=12, _
Criteria1:=Sheets("Report Creator").Cells(2, 2).Value

Sheets("Actions").Range("A1").AutoFilter Field:=13, _
Criteria1:=Sheets("Report Creator").Cells(2, 3).Value

Sheets("Report Creator").Select

Sheets("Safety Accountability Dashboard").Visible = True

Sheets("Safety Accountability Dashboard").Select
 
Upvote 0
Ok, how about
VBA Code:
With Sheets("Report Creator")
   Sheets("Actions").Range("A1").AutoFilter 11, .Cells(2, 1).Value
   
   If .Cells(2, 2) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 12, .Cells(2, 2).Value
   End If
   If .Cells(2, 3) <> "" Then
      Sheets("Actions").Range("A1").AutoFilter 13, .Cells(2, 3).Value
   End If
   .Select
End With
 
Upvote 0
Solution
Hey Fluff! It works awesome mate, you're a legend!
Cheers,
Mike
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You're welcome & thanks for the feedback.
Hey again Fluff,

There is a tiny glitch in the code you gave me. Sometimes when someone is selecting different levels, and hitting the reset button it will give sometimes the code won't work. An example of this is: I select "SEQ" in the first drop down, then I select "SEQ Operations" in the second drop down and hit the 'Generate Report' button, everything works.

I then reset the report and then select "Regional" in the first drop down (I won't select anything from the remaining drop downs) and then I hit the 'Generate Report' button, however this time all the sheets have their headers but no data. When I unhide and look at the master sheets, it is also filtered and showing nothing; basically it has my most recent first drop value of "Regional", however the table is also being filtered by the most recent second dropdown "SEQ Operations"; however there is no pattern (lack of a better word) then
1. "Regional"
2. "SEQ Operations"

The only way to clear "SEQ Operations" and get back to a working version is to select a value from the second drop down that matches "Regional" e.g. "Operations North". From there I can hit the 'Reset' button and can carry on.

Do you have any thoughts?

I'll put the code below that I think is not resetting the filtered columns;

VBA Code:
Sub Report_Builder()


With Sheets("Report Creator")
Sheets("Actions").Range("A1").AutoFilter 11, .Cells(2, 1).Value

If .Cells(2, 2) <> "" Then
Sheets("Actions").Range("A1").AutoFilter 12, .Cells(2, 2).Value
End If
If .Cells(2, 3) <> "" Then
Sheets("Actions").Range("A1").AutoFilter 13, .Cells(2, 3).Value
End If
.Select
End With


With Sheets("Report Creator")
Sheets("Investigations").Range("A1").AutoFilter 14, .Cells(2, 1).Value

If .Cells(2, 2) <> "" Then
Sheets("Investigations").Range("A1").AutoFilter 15, .Cells(2, 2).Value
End If
If .Cells(2, 3) <> "" Then
Sheets("Investigations").Range("A1").AutoFilter 16, .Cells(2, 3).Value
   End If

Sheets("Report Creator").Select

Sheets("Safety Accountability Dashboard").Visible = True

Sheets("Safety Accountability Dashboard").Select



        Dim DbExtract, DuplicateRecords As Worksheet
Set DbExtract = ThisWorkbook.Sheets("Investigations")
    Set DuplicateRecords = ThisWorkbook.Sheets("My team's investigations")

    Sheets("Safety Accountability Dashboard").Select
Sheets("Investigations").Visible = True
Sheets("Investigations").Select
    Sheets("My team's investigations").Visible = True

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
DuplicateRecords.Cells(1, 1).PasteSpecial

Sheets("Investigations").Select
ActiveWindow.SelectedSheets.Visible = False
Selection.AutoFilter
Selection.WrapText = True
'Rows(1).Select
    'Selection.Font.Bold = True


Set DbExtract = ThisWorkbook.Sheets("Actions")
    Set DuplicateRecords = ThisWorkbook.Sheets("My team's actions")

    Sheets("Safety Accountability Dashboard").Select
Sheets("Actions").Visible = True
Sheets("Actions").Select
    Sheets("My team's actions").Visible = True

    DbExtract.Range("A1:BF9999").SpecialCells(xlCellTypeVisible).Copy
DuplicateRecords.Cells(1, 1).PasteSpecial

Sheets("Actions").Select
ActiveWindow.SelectedSheets.Visible = False
Selection.AutoFilter
Selection.WrapText = True
'Rows(1).Select
'Selection.Font.Bold = True

Sheets("Report Creator").Select
ActiveWindow.SelectedSheets.Visible = False
    Sheets("Safety Accountability Dashboard").Select


End

End With

End Sub



Or it could be the reset code, which is probably the best place to put it, see below:
VBA Code:
Sub Reset()

    Sheets("My team's actions").Select
Selection.ClearContents
Sheets("My team's actions").Select
ActiveWindow.SelectedSheets.Visible = False
Cells.Select
Selection.ClearContents
Sheets("My team's investigations").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Safety Accountability Dashboard").Select
Sheets("Report Creator").Visible = True
Sheets("Safety Accountability Dashboard").Select
ActiveWindow.SelectedSheets.Visible = False
Range("A2:C2").Select
Range("C2").Activate
Selection.ClearContents
    Range("A1").Select

End Sub
 
Upvote 0
How about
VBA Code:
   With Sheets("Report Creator")
      Sheets("Actions").Range("A1").AutoFilter 11, .Cells(2, 1).Value
      
      If .Cells(2, 2) <> "" Then
         Sheets("Actions").Range("A1").AutoFilter 12, .Cells(2, 2).Value
      Else
         Sheets("Actions").Range("A1").AutoFilter 12
      End If
      If .Cells(2, 3) <> "" Then
         Sheets("Actions").Range("A1").AutoFilter 13, .Cells(2, 3).Value
      Else
         Sheets("Actions").Range("A1").AutoFilter 13
      End If
      .Select
   End With
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top