Not to filter a table when there is no matching text

bosko2

New Member
Joined
Nov 3, 2020
Messages
14
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
What code do you currently have?
 

bosko2

New Member
Joined
Nov 3, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
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
 
Solution

bosko2

New Member
Joined
Nov 3, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hey Fluff! It works awesome mate, you're a legend!
Cheers,
Mike
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

bosko2

New Member
Joined
Nov 3, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,868
Office Version
  1. 365
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,774
Messages
5,574,157
Members
412,574
Latest member
shadowfighter666
Top