help with converting 3 separate "If" statements to 1 statement with 2 "IfElse"

K

kbishop94

Active Member
Joined
Dec 5, 2016
Messages
458
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I have tried multiple variations of trying to shorten the script for these 3 events (as they are all closely related) mainly trying to utilize a couple of "ElseIf" statements....

my current code:
VBA Code: 
'*************************************************************************************************************************************************************************
' CHECKBOXES FOR SHOWING WHO'S FAULT THE INCIDENT IS (SEATEX, NOT SEATEX OR NOT KNOWN):
' SHOW ONLY INCIDENTS THAT ARE *SEATEX'* FAULT:
Dim rSEA As Long
    rSEA = Worksheets("REPORTS").Range(Cells(30, 5), Cells(rRow, 5)).SpecialCells(xlCellTypeVisible).Cells.Count
        If rSEA <= 1 Then
'
        Else
            If chkOurFault.value = True Then
                For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
                    If Cell.value = "SF" And Rows(Cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *SEATEX FAULT* THEN KEEP THAT ROW UNHIDDEN:
                       Rows(Cell.Row).Hidden = False
                Else                                                                   
                        Rows(Cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
                    End If
            Next
        End If
    End If
'**********************************************************************
' SHOW ONLY INCIDENTS THAT ARE NOT *SEATEX'* FAULT:
        If rSEA <= 1 Then
'
        Else
            If chkNotOurFault.value = True Then
                For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
                    If Cell.value = "NF" And Rows(Cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *NOT* SEATEX FAULT THEN KEEP THAT ROW UNHIDDEN:
                       Rows(Cell.Row).Hidden = False
                Else                                                                    
                        Rows(Cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
                    End If
            Next
        End If
    End If
'**********************************************************************
' SHOW ONLY INCIDENTS WHERE THE FAULT IS UNKNOWN:
        If rSEA <= 1 Then
'
        Else
            If chkUnknownFault.value = True Then
                For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
                    If Cell.value = "UF" And Rows(Cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *UNKNOWN FAULT* THEN THEN KEEP THAT ROW UNHIDDEN:
                       Rows(Cell.Row).Hidden = False
                Else                                                                                              
                        Rows(Cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
                    End If
            Next
        End If
    End If
'

What the code is doing:

1. code is executed via a userform where the user can choose 1 of 3 options:
chkBoxes.JPG

2. the code looks at rows that are currently VISIBLE in column "AB"/28 when the code is ran.

3. if the row contains the string that corresponds to each checkbox, then it allows that row to remain UNHIDDEN and hides all other rows that dont match.
fault.JPG
4. it does this for each of the 3 options available: (1. "seatex fault" = "SF", 2. "not seatex fault" = "NF" and 3. "unknown fault" = "UF" )

So my question is, is there a way to shorten/combine these 3 statements into 1 statement such as using a "ElseIf" method?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
One way
VBA Code: 
   Dim rSEA As Long
   rSEA = Worksheets("REPORTS").Range(Cells(30, 5), Cells(rRow, 5)).SpecialCells(xlCellTypeVisible).Cells.Count
   If rSEA > 1 Then
      If chkOurFault.Value = True Then
         For Each cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
            If cell.Value = "SF" And Rows(cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *SEATEX FAULT* THEN KEEP THAT ROW UNHIDDEN:
               Rows(cell.Row).Hidden = False
            Else
               Rows(cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
            End If
         Next
      ElseIf chkNotOurFault.Value = True Then
         For Each cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
            If cell.Value = "NF" And Rows(cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *NOT* SEATEX FAULT THEN KEEP THAT ROW UNHIDDEN:
               Rows(cell.Row).Hidden = False
            Else
               Rows(cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
            End If
         Next
      ElseIf chkUnknownFault.Value = True Then
         For Each cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
            If cell.Value = "UF" And Rows(cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *UNKNOWN FAULT* THEN THEN KEEP THAT ROW UNHIDDEN:
               Rows(cell.Row).Hidden = False
            Else
               Rows(cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
            End If
         Next
      End If
   End If
 
Upvote 0
Another way
VBA Code: 
   rSEA = Worksheets("REPORTS").Range(Cells(30, 5), Cells(rRow, 5)).SpecialCells(xlCellTypeVisible).Cells.Count
   If rSEA > 1 Then
      For Each cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
         If chkOurFault.Value = True Then
            If cell.Value = "SF" And Rows(cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *SEATEX FAULT* THEN KEEP THAT ROW UNHIDDEN:
               Rows(cell.Row).Hidden = False
            Else
               Rows(cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
            End If
         ElseIf chkUnknownFault.Value = True Then
            If cell.Value = "NF" And Rows(cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *NOT* SEATEX FAULT THEN KEEP THAT ROW UNHIDDEN:
               Rows(cell.Row).Hidden = False
            Else
               Rows(cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
            End If
         ElseIf chkUnknownFault.Value = True Then
            If cell.Value = "UF" And Rows(cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *UNKNOWN FAULT* THEN THEN KEEP THAT ROW UNHIDDEN:
               Rows(cell.Row).Hidden = False
            Else
               Rows(cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
            End If
         End If
      Next
   End If
 
Upvote 0
If they are mutually exclusive, wouldn't something like this work ?
VBA Code: 
Sub test_reduced()
'*************************************************************************************************************************************************************************
' CHECKBOXES FOR SHOWING WHO'S FAULT THE INCIDENT IS (SEATEX, NOT SEATEX OR NOT KNOWN):
' SHOW ONLY INCIDENTS THAT CODE SELECTED
Dim rSEA As Long
Dim strFault As String

    If chkOurFault.Value = True Then
        strFault = "SF"
    ElseIf chkNotOurFault.Value = True Then
        strFault = "NF"
    ElseIf chkUnknownFault.Value = True Then
        strFault = "UF"
    End If
    
    rSEA = Worksheets("REPORTS").Range(Cells(30, 5), Cells(rRow, 5)).SpecialCells(xlCellTypeVisible).Cells.Count
        If rSEA <= 1 Then
'
        Else
            If chkOurFault.Value = True Then
                For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
                    If Cell.Value = strFault _
                            And Rows(Cell.Row).Hidden = False Then
                        
                        Rows(Cell.Row).Hidden = False
                    Else
                        Rows(Cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
                    End If
                Next
            End If
        End If

End Sub
 
Upvote 0
Alex Blakenburg said:
If they are mutually exclusive, wouldn't something like this work ?
VBA Code: 
Sub test_reduced()
'*************************************************************************************************************************************************************************
' CHECKBOXES FOR SHOWING WHO'S FAULT THE INCIDENT IS (SEATEX, NOT SEATEX OR NOT KNOWN):
' SHOW ONLY INCIDENTS THAT CODE SELECTED
Dim rSEA As Long
Dim strFault As String

    If chkOurFault.Value = True Then
        strFault = "SF"
    ElseIf chkNotOurFault.Value = True Then
        strFault = "NF"
    ElseIf chkUnknownFault.Value = True Then
        strFault = "UF"
    End If
   
    rSEA = Worksheets("REPORTS").Range(Cells(30, 5), Cells(rRow, 5)).SpecialCells(xlCellTypeVisible).Cells.Count
        If rSEA <= 1 Then
'
        Else
            If chkOurFault.Value = True Then
                For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
                    If Cell.Value = strFault _
                            And Rows(Cell.Row).Hidden = False Then
                       
                        Rows(Cell.Row).Hidden = False
                    Else
                        Rows(Cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
                    End If
                Next
            End If
        End If

End Sub
Click to expand...

Thanks, Alex. I tried yours first as it was the shortest. I first tried the first 'If' ("SF"), and it correctly hid all the non "SF" rows and kept the "SF" rows unhidden.
But when trying the other 2, it returned the same result as choosing none of the checkboxes (it showed all 3... "SF", "NF" and "UF". ?
 
Upvote 0
Fluff said:
Another way
VBA Code: 
   rSEA = Worksheets("REPORTS").Range(Cells(30, 5), Cells(rRow, 5)).SpecialCells(xlCellTypeVisible).Cells.Count
   If rSEA > 1 Then
      For Each cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
         If chkOurFault.Value = True Then
            If cell.Value = "SF" And Rows(cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *SEATEX FAULT* THEN KEEP THAT ROW UNHIDDEN:
               Rows(cell.Row).Hidden = False
            Else
               Rows(cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
            End If
         ElseIf chkUnknownFault.Value = True Then
            If cell.Value = "NF" And Rows(cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *NOT* SEATEX FAULT THEN KEEP THAT ROW UNHIDDEN:
               Rows(cell.Row).Hidden = False
            Else
               Rows(cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
            End If
         ElseIf chkUnknownFault.Value = True Then
            If cell.Value = "UF" And Rows(cell.Row).Hidden = False Then         ' IF THE INCIDENT IS *UNKNOWN FAULT* THEN THEN KEEP THAT ROW UNHIDDEN:
               Rows(cell.Row).Hidden = False
            Else
               Rows(cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
            End If
         End If
      Next
   End If
Click to expand...

Thanks, Fluff. Both of yours worked great. Is there one that would be better or preferable more than the other?

The reason I asked this question is that I have several other very similar 'options' in my user form that a user can choose when searching records that I will now be able to use this same technique for those as well (for shortening mutliple "If" statements throughout my code.) I thought I'd ask which one might be preferable so I can make sure I'm using the most efficient code where I can.
 
Upvote 0
With the code Alex provided you would need to delete these two lines
Rich (BB code): 
            If chkOurFault.Value = True Then
                For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
                    If Cell.Value = strFault _
                            And Rows(Cell.Row).Hidden = False Then
                       
                        Rows(Cell.Row).Hidden = False
                    Else
                        Rows(Cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
                    End If
                Next
            End If
        End If
 
Upvote 0
Solution
Fluff said:
With the code Alex provided you would need to delete these two lines
Rich (BB code): 
            If chkOurFault.Value = True Then
                For Each Cell In ActiveWorkbook.Worksheets("REPORTS").Range(Cells(31, 28), Cells(rRow, 28)).SpecialCells(xlCellTypeVisible)
                    If Cell.Value = strFault _
                            And Rows(Cell.Row).Hidden = False Then
                      
                        Rows(Cell.Row).Hidden = False
                    Else
                        Rows(Cell.Row).Hidden = True                                    ' ELSE NO CURRENTLY UNHIDDEN RECORDS MATCH THE CHKBOX SELECTION, SO HIDE ALL OF THOSE ROWS
                    End If
                Next
            End If
        End If
Click to expand...

That did the trick! Thank you Fluff, and THANK YOU Alex. :LOL: (y)
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
help with VBA to find specific text within a string and within a specific range
Replies
1
Views
2K
kbishop94
K
J
  • Question
VBA Macro for hiding and unhiding rows is rearranging cells
2
Replies
14
Views
934
dmt32
D
K
  • Solved
Script not executing
Replies
3
Views
222
Joe4
Joe4
K
  • Solved
Code for finding a specific value within a range
Replies
3
Views
372
BenGee
B
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
400
Legacy 352679
L

Forum statistics

Threads
1,214,983
Messages
6,122,583
Members
449,089
Latest member
Motoracer88

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