Hiding Rows Based on Matching Value in Specific Cells

cheesiepoof05

Board Regular
Joined
Dec 30, 2010
Messages
82
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
 

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.
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
 
Upvote 0
Solution
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.
 
Upvote 0
The numbers after the Autofilter specify which column to filter, so 1 is the 1st column in the filtered range.
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
What is your system date setting?
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
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