CODEWITHSUNDEEP
excelvbamultiple-columnslogical-operatorsmultiple-conditions
knefie
knefie

Reputation: 95

Combine criteria / macros VBA

Sub Macro_1()
Dim RowIndex, x As Integer
Dim RowArr() As String
RowIndex = "8,9,11,12,13,14,15,16,17"
RowArr = Split(RowIndex, ",")
Application.ScreenUpdating = False

  Sheets("Test").Select
  
  For x = 0 To UBound(RowArr)
    ActiveSheet.Rows().AutoFilter Field:=CInt(RowArr(x))
  Next
  ActiveSheet.Rows().AutoFilter Field:=10, Criteria1:="<>"
  Sheets("Test").Select
  Call SetClassFields("1")
  Call Macro_Filter
  Application.ScreenUpdating = True

End Sub
Sub Macro_2()
Dim RowIndex, x As Integer
Dim RowArr() As String
RowIndex = "8,9,10,12,13,14,15,16,17"
RowArr = Split(RowIndex, ",")
Application.ScreenUpdating = False

  Sheets("Test").Select
  
  For x = 0 To UBound(RowArr)
    ActiveSheet.Rows().AutoFilter Field:=CInt(RowArr(x))
  Next
  ActiveSheet.Rows().AutoFilter Field:=11, Criteria1:="<>"
  Sheets("Test").Select
  Call SetClassFields("2")
  Call Macro_Filter
  Application.ScreenUpdating = True

End Sub

I have a table with several criteria. Until now, only one criterion had to be filtered at a time. Now several columns that contain the X should be filtered. Now I'm thinking of how to combine these two macros.

Upvotes: 1

Views: 59

Answers (1)

ZygD
ZygD

Reputation: 24386

Well, even if you're a beginner, you should know what YOUR code does. It seems you put things, not really knowing what they do. For example, the following does absolutely nothing.

For x = 0 To UBound(RowArr)
  ActiveSheet.Rows().AutoFilter Field:=CInt(RowArr(x))
Next

It can be safely removed.

It means x, RowIndex and RowArr can also be removed, because you don't use them elsewhere.

Also, there is no need to use .Select, as any sheet or range can be referenced without actually selecting it. In your code we can see Sheets("Test").Select two times. The second one does absolutely nothing. The first one is advised to be removed. When you remove, in the following line instead of ActiveSheet you can use Sheets("Test") which is the same reference, but without unnecessary Sheets("Test").Select.

Your fixed macros may look like this:

Sub Macro_1()
  Application.ScreenUpdating = False
  
  Sheets("Test").Rows().AutoFilter Field:=10, Criteria1:="<>"
  Call SetClassFields("1")
  Call Macro_Filter
  
  Application.ScreenUpdating = True
End Sub

Sub Macro_2()
  Application.ScreenUpdating = False

  Sheets("Test").Rows().AutoFilter Field:=11, Criteria1:="<>"
  Call SetClassFields("2")
  Call Macro_Filter
  
  Application.ScreenUpdating = True
End Sub

I hope I'm correct, because you may have shortened your code for example purposes...

Now, to your question. I assume you run your scripts one after another. So the main code parts may just be put inside one script like this:

Sub Macro_3()
  Application.ScreenUpdating = False
  
  Sheets("Test").Rows().AutoFilter Field:=10, Criteria1:="<>"
  Call SetClassFields("1")
  Call Macro_Filter
  Sheets("Test").Rows().AutoFilter Field:=11, Criteria1:="<>"
  Call SetClassFields("2")
  Call Macro_Filter
  
  Application.ScreenUpdating = True
End Sub

If you want to be able to choose which path to go (OR condition), you need to give a parameter to the function (let's call the parameter i).

Sub caller()
  Call Macro_x(1)
End Sub

Private Sub Macro_x(i As Integer)
  Application.ScreenUpdating = False
  
  If i = 1 Then
    Sheets("Test").Rows().AutoFilter Field:=10, Criteria1:="<>"
  ElseIf i = 2 Then
    Sheets("Test").Rows().AutoFilter Field:=11, Criteria1:="<>"
  Else
    MsgBox "parameter i was not defined"
    Exit Sub
  End If
  Call SetClassFields(CStr(i))
  Call Macro_Filter
  
  Application.ScreenUpdating = True
End Sub

In this code you see the main function (caller) and another (Marcro_x), which is called inside caller. You must provide a parameter in parentheses when you call Macro_x - 1 or 2. Depending on the parameter provided, the sub will do slightly different things.

Upvotes: 2

Related Questions