Combine input box (multiple criteria)

[gomes123]

My original excel macro is here, where I have to manually go into the excel macro to adjust the values

 If a(i, 1) <= 0.3 Or a(i, 8) < 30 Or a(i, 11) < 50000 Then b(i, 1) = 1

Option Explicit
Sub Delete_Rows_Multi_Criteria()
    Application.ScreenUpdating = False
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    Dim LRow As Long, LCol As Long, i As Long, a, b
    LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
    
    a = Range(ws.Cells(2, 3), ws.Cells(LRow, 13))
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
        If a(i, 1) <= 0.3 Or a(i, 8) < 30 Or a(i, 11) < 50000 Then b(i, 1) = 1
    Next i
    
    ws.Cells(2, LCol).Resize(UBound(a)) = b
    i = WorksheetFunction.Sum(ws.Columns(LCol))
    If i > 0 Then
        ws.Range(ws.Cells(2, 1), ws.Cells(LRow, LCol)).Sort Key1:=ws.Cells(2, LCol), _
        order1:=xlAscending, Header:=xlNo
        ws.Cells(2, LCol).Resize(i).EntireRow.Delete
    End If
    Application.ScreenUpdating = True
End Sub

I've adjusted the macro so that there is an input box now, but is there a way to combine the input box, where only 1 input box comes up where I can enter the 3 criteria, instead of entering it 1 by 1? Thanks!

Option Explicit
Sub PopUp()
    Application.ScreenUpdating = False
    Dim criteria1 As Double
    Dim criteria2 As Double
    Dim criteria3 As Double
    

    criteria1 = InputBox("Enter value for criteria 1 (<= 0.3):")
    criteria2 = InputBox("Enter value for criteria 2 (< 30):")
    criteria3 = InputBox("Enter value for criteria 3 (< 50000):")
    
    Dim ws As Worksheet
    Set ws = Worksheets("Sheet1")
    Dim LRow As Long, LCol As Long, i As Long, a, b
    LRow = ws.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row
    LCol = ws.Cells.Find("*", , xlFormulas, , xlByColumns, xlPrevious).Column + 1
    
    a = Range(ws.Cells(2, 3), ws.Cells(LRow, 13))
    ReDim b(1 To UBound(a), 1 To 1)
    For i = 1 To UBound(a)
        If a(i, 1) <= criteria1 Or a(i, 8) < criteria2 Or a(i, 11) < criteria3 Then b(i, 1) = 1
    Next i
    
    ws.Cells(2, LCol).Resize(UBound(a)) = b
    i = WorksheetFunction.Sum(ws.Columns(LCol))
    If i > 0 Then
        ws.Range(ws.Cells(2, 1), ws.Cells(LRow, LCol)).Sort Key1:=ws.Cells(2, LCol), _
        order1:=xlAscending, Header:=xlNo
        ws.Cells(2, LCol).Resize(i).EntireRow.Delete
    End If
    Application.ScreenUpdating = True
End Sub

 

[Akuini]

How about using a userform along with 3 textboxes?

 

[gomes123]

How about using a userform along with 3 textboxes?

Thanks! I wasn't aware of the term "userform" or the differences between an inputbox and userform.

But yes, it does seem better to use a userform. Is there an efficient way to code it in excel macro? Thanks!

 

[MikeVol]

Hi @gomes123.
Change your code with these lines, add them:

    ' The rest of your code is above
    Dim inputString As String
    inputString = InputBox("Enter values for the three criteria, separated by commas (eg.: 0.3, 29, 49999)")

    Dim inputArray  As Variant
    inputArray = Split(inputString, ",")

    If UBound(inputArray) >= 2 Then
        criteria1 = Val(Trim(inputArray(0)))
        criteria2 = Val(Trim(inputArray(1)))
        criteria3 = Val(Trim(inputArray(2)))
    Else
        MsgBox "Please enter all three values for the criteria."
        Exit Sub
    End If

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' The rest of your code is below

 

[gomes123]

Hi @gomes123.
Change your code with these lines, add them:

    ' The rest of your code is above
    Dim inputString As String
    inputString = InputBox("Enter values for the three criteria, separated by commas (eg.: 0.3, 29, 49999)")

    Dim inputArray  As Variant
    inputArray = Split(inputString, ",")

    If UBound(inputArray) >= 2 Then
        criteria1 = Val(Trim(inputArray(0)))
        criteria2 = Val(Trim(inputArray(1)))
        criteria3 = Val(Trim(inputArray(2)))
    Else
        MsgBox "Please enter all three values for the criteria."
        Exit Sub
    End If

    Dim ws          As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' The rest of your code is below

Thanks so much! It works perfectly!