Hiding Rows Based on Matching Value in Specific Cells

cheesiepoof05

Board Regular
Joined
Dec 30, 2010
Messages
78
I'm trying to set up a landing page where data selections can be entered and then based on those selections it hides records on the data set tab so that only what was selected on the landing page is shown on the data set tab, unless the landing page cell is left blank, then that column wouldn't be limited. So the workbook would open to "landing page" tab, they would populate "Dog", "Red", and leave the number cell blank. When the process button is pushed, it hides all records on the "Data Set" tab where column A is not "Dog", column B is not "Red", and since Number was left blank in this scenario, it wouldn't use that as a factor this time. We're trying to make a "cleaner" option than the user going to the data tab and limiting each column themselves. Thank you for your help.

1605880345586.png
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,772
Office Version
  1. 365
Platform
  1. Windows
Maybe
VBA Code:
Sub cheesiepoof()
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Sheet1")
   With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      If Ws.Range("B2") <> "" Then .Range("A1").AutoFilter 1, Ws.Range("B2")
      If Ws.Range("B3") <> "" Then .Range("A1").AutoFilter 2, Ws.Range("B3")
      If Ws.Range("B4") <> "" Then .Range("A1").AutoFilter 3, Ws.Range("B4")
   End With
End Sub
 
Solution

cheesiepoof05

Board Regular
Joined
Dec 30, 2010
Messages
78
Maybe
VBA Code:
Sub cheesiepoof()
   Dim Ws As Worksheet
  
   Set Ws = Sheets("Sheet1")
   With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      If Ws.Range("B2") <> "" Then .Range("A1").AutoFilter 1, Ws.Range("B2")
      If Ws.Range("B3") <> "" Then .Range("A1").AutoFilter 2, Ws.Range("B3")
      If Ws.Range("B4") <> "" Then .Range("A1").AutoFilter 3, Ws.Range("B4")
   End With
End Sub
Thanks so much, this appears to do the trick. Would you mind showing me what part of this macro is directing it to look at the specific column on the data tab? I will need that to try to make it more elaborate for my solution and don't follow that part of the logic. Thanks again.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,772
Office Version
  1. 365
Platform
  1. Windows
The numbers after the Autofilter specify which column to filter, so 1 is the 1st column in the filtered range.
 

cheesiepoof05

Board Regular
Joined
Dec 30, 2010
Messages
78

ADVERTISEMENT

The numbers after the Autofilter specify which column to filter, so 1 is the 1st column in the filtered range.
Aha, gotcha. Thanks so much for your help!
 

Fluff

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

cheesiepoof05

Board Regular
Joined
Dec 30, 2010
Messages
78

ADVERTISEMENT

One last question. If I want to move my data down on the data tab and have the filter begin on say row 10, how would I do that?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,772
Office Version
  1. 365
Platform
  1. Windows
Just change A1 to to match the first cell in your header row.
 

cheesiepoof05

Board Regular
Joined
Dec 30, 2010
Messages
78
Maybe
VBA Code:
Sub cheesiepoof()
   Dim Ws As Worksheet
  
   Set Ws = Sheets("Sheet1")
   With Sheets("Data")
      If .AutoFilterMode Then .AutoFilterMode = False
      If Ws.Range("B2") <> "" Then .Range("A1").AutoFilter 1, Ws.Range("B2")
      If Ws.Range("B3") <> "" Then .Range("A1").AutoFilter 2, Ws.Range("B3")
      If Ws.Range("B4") <> "" Then .Range("A1").AutoFilter 3, Ws.Range("B4")
   End With
End Sub

I just noticed that this does not work for dates for some reason. Even when they are formatted the same and have exactly the same text in the formula bar. Any ideas on a fix for that?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,772
Office Version
  1. 365
Platform
  1. Windows
What is your system date setting?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,088
Messages
5,628,603
Members
416,327
Latest member
Chimay

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
Top