code for hiding rows based on selection from combobox... got it working(!) but.....

K

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
476
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
My Combobox lists the customers that are shown in column G (see pics below). Based on the value that is selected, all the rows except those listing that customer(value) are hidden.
So... it all works fine, *but* I cant figure out how when the first choice of "Show all Customers" is selected that ALL the rows be shown (unhidden)

my code:

Code: 
WORKBOOK CODE:

Sub Workbook_Open()


Worksheets("Seatex Incident Log").Activate


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


With Rows("10:356")
    .Hidden = False
End With
    
With Sheets("Seatex Incident Log").ComboBox1
.AddItem "Show all Customers"
.AddItem "Internal Seatex Incident"
.AddItem "AEGIS"
.AddItem "A.I.M.P"
.AddItem "A-1 CHEMICAL EQUIP. & SUPPL"
.AddItem "ACP"

(this continues for another 300+ customers)


.Value = "Show all Customers"


End With


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
    
Private Sub Workbook_Close()

SHEET1 CODE:

Private Sub ComboBox1_Change()


Application.Calculation = xlAutomatic
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


Dim DataCriteria As String


Dim rCol As Long
    rCol = ActiveSheet.UsedRange.Rows.Count
    For Each Cell In Range(Cells(10, 7), Cells(rCol, 7))


DataCriteria = ComboBox1.Value


If Cell = DataCriteria Then Rows(Cell.Row).Hidden = False Else Rows(Cell.Row).Hidden = True


Next


Application.Calculation = xlAutomatic
Application.ScreenUpdating = False


End Sub



Application.DisplayFullScreen = False
ActiveWindow.DisplayHeadings = True


With Rows("10:356")
    .Hidden = False
End With


End Sub



my spreadsheet (for visual explanation)

spreadsheet before making a selection (showing all rows):

2yuwpyr.jpg



spreadsheet after making a selection:


2uzz31v.jpg



currently when choosing "show all customers" (I need this so when the selection "show all customers" is chosen ALL rows are shown.)

1pyyl2.jpg
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
How about
Code: 
Private Sub ComboBox1_Change()

Dim rCol As Long
Dim DataCriteria As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


ActiveWindow.ScrollColumn = 1


rCol = ActiveSheet.UsedRange.Rows.Count
If ComboBox1.Value = "Show all Customers" Then
   DataCriteria = "*"
Else
   DataCriteria = ComboBox1.Value
End If
Range("G9:G" & rCol).AutoFilter 1, DataCriteria


Application.Calculation = xlAutomatic
Application.ScreenUpdating = True


End Sub
 
Last edited:
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
Ok, I was playing around with it and attempting to add another Combobox (Combobox2) to apply the same filtering technique but for Column J ("Issued by"). So I see that its not as simple as just changing the names/values to match the new criteria.(?) Is there a 'trick' when trying to have mutiple combo boxes filtering different columns? Thanks (again)

My new code:

Code: 
Private Sub ComboBox1_Change()

Dim rCol As Long
Dim DataCriteria As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

ActiveWindow.ScrollColumn = 1

rCol = ActiveSheet.UsedRange.Rows.Count

If ComboBox1.Value = "Show all Customers" Then
   DataCriteria = "*"

Else
   DataCriteria = ComboBox1.Value

End If

Range("G9:G" & rCol).AutoFilter 1, DataCriteria

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub



Private Sub ComboBox2_Change()

Dim sCol As Long
Dim DataCriteria1 As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

ActiveWindow.ScrollColumn = 1

sCol = ActiveSheet.UsedRange.Rows.Count
If ComboBox2.Value = "Show all: Issued By" Then
   DataCriteria1 = "*"

Else
   DataCriteria1 = ComboBox2.Value
End If

Range("J9:J" & sCol).AutoFilter 1, DataCriteria1

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub
 
Upvote 0
What are the 1st & last columns in your data?
 
Upvote 0
Fluff said:
What are the 1st & last columns in your data?
Click to expand...

Column A and Column Q. Column J is what I was trying to apply the filter to.

2ypb2a0.jpg


Here is the additional code from the Workbook module that I left out from my previous post:

Code: 
Sub Workbook_Open()


Worksheets("Seatex Incident Log").Activate


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False




ActiveWindow.ScrollColumn = 1


    
With Rows("10:356")
    .Hidden = False
End With


    
With Sheets("Seatex Incident Log").ComboBox1
.AddItem "Show all Customers"
.AddItem "Internal Seatex Incident"
.AddItem "AEGIS"
.AddItem "A.I.M.P"
.AddItem "A-1 CHEMICAL EQUIP. & SUPPL"
.AddItem "ACP"
.AddItem "AEGIS"
.AddItem "ALACK"
.AddItem "ALBEMARLE"
.AddItem "AMC"
.AddItem "AMTRAK"

continues....  (300+ more)

.Value = "Show all Customers"
End With


With Sheets("Seatex Incident Log").ComboBox2
.AddItem "Show all: Issued By"
.AddItem "AL BROOKS"
.AddItem "ANSELMO GARCIA"
.AddItem "CAMERON MILLER"
.AddItem "CAROLYN GONZALEZ"
.AddItem "CINDY MOREAU"
.AddItem "CINDY GARCIA"

continues...  (50+ more names)


.Value = "Show all: Issued By"
End With


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub
    
Private Sub Workbook_Close()


Application.DisplayFullScreen = False
ActiveWindow.DisplayHeadings = True


With Rows("10:356")
    .Hidden = False
End With


End Sub
 
Upvote 0
Try this
Code: 
Private Sub ComboBox1_Change()

Dim rCol As Long
Dim DataCriteria As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

ActiveWindow.ScrollColumn = 1

rCol = ActiveSheet.UsedRange.Rows.Count

If ComboBox1.Value = "Show all Customers" Then
   DataCriteria = "*"

Else
   DataCriteria = ComboBox1.Value

End If

Range("A9:Q" & rCol).AutoFilter 7, DataCriteria

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub



Private Sub ComboBox2_Change()

Dim sCol As Long
Dim DataCriteria1 As String

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

ActiveWindow.ScrollColumn = 1

sCol = ActiveSheet.UsedRange.Rows.Count
If ComboBox2.Value = "Show all: Issued By" Then
   DataCriteria1 = "*"

Else
   DataCriteria1 = ComboBox2.Value
End If

Range("A9:Q" & sCol).AutoFilter 10, DataCriteria1

Application.Calculation = xlAutomatic
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Fluff said:
Try this
Click to expand...


At first it didnt work... I was getting "Run-time error '1004' AutoFilter method of Range Class Failed"

But I realized that once I highlighted the row with the headers and selected the Filter button on the Data tab, it works fine. But, I dont really understand why. The code before you posted (when it was just 1 column) worked fine without having to "turn on" the Filter on the Data tab(?) Its not a problem as the Comboboxs both work now as a way for the user to select the criteria he/she needs, it was just a little 'cleaner' looking the other way. thank you so much for all your help. -Keith
 
Upvote 0
I just found another issue... it doesnt like blanks in the columns that are filtered. Example: I select a customer, it filters and hides all the ones that do not match the criteria... but then when i select the 'show all customers' choice, it only shows the rows that have a value/text in that column. (which makes sense now that I have thought about it because you told it with your code to look for a wildcard ("*") and that doesnt take inconsideration blanks.
Is it possible to have blanks show up (unhide) as well as the wildcard? Thank you (again, and again... :) )
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Question Question
Have the code select the cell that relates to what was searched for
Replies
0
Views
226
ipbr21054
I
I
  • Question Question
Find customer in column & link to his pdf
2
Replies
16
Views
605
NoSparks
N
M
  • Question Question
VBA Subtotal 47,000+ Rows
Replies
1
Views
228
igold
igold
J
  • Question Question
Append data to master file
Replies
6
Views
338
Alex Blakenburg
A
I
  • Question Question
Add ID number next to customer row from userform transfer
Replies
2
Views
308
Joe4
Joe4

Forum statistics

Threads
1,223,896
Messages
6,175,264
Members
452,627
Latest member
KitkatToby

Share this page

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