how do I pass Inputbox data to a formula within code?

[jmckeone]

There are certain recurring tasks I do and rather than have to write new code each time I'm thinking it would be more efficient to have generic code and have the code prompt the user for input to tailor the code to the current application.

For example convert following code so that each place VARIABLE appears that the value could be supplied by the user via inputbox.

Sub RemoveRows()

    Dim NumRows, iLine As Integer
    
'Select the usedRange
    ActiveSheet.UsedRange.Select
    
'Get the Number of the rows used
    NumRows = ActiveSheet.UsedRange.Rows.Count

'Delete all rows ending with VARIABLE1 where value in Column 'VARIABLE2 is VARIABLE3

    For iLine = NumRows To VARIABLE1 Step -1
        If Range("VARIABLE2" & iLine).Value = "VARIABLE3" Then
               Rows(iLine).EntireRow.Delete
        End If
   Next iLine
End Sub

 

[kendavid]

remove rows and input box

I think this does what you are looking for. Since your VARIABLEA is both the row end criteria and column number, the inputbox must be an integer (and the end of the row must be an integer) or the code will fail on a type mismatch error.

Also for a similar reason, the code as is will not always be able to delete any row. For example, if you wanted to delete a row ending with the number "4" that is in the 3rd row, the code will miss it because VARIABLEA says to look only in row 4 and beyond. I have taken the liberty of adding a commented out line that can delete any row. Just switch the comment to enable that line.

Also I tried to include VARIABLE2 in the prompt for inputbox 3 but I was unable to get it to work. It seemed like a nice addition and some tinkering would probably get it to work but I left the line commented out. Maybe someone else has an idea.

Sub RemoveRows()

    Dim NumRows, iLine As Integer
    
    Dim Message1, Title1, variable1
    Message1 = "Delete all rows ending with"    ' Set prompt.
    Title1 = "InputBox Demo"    ' Set title.
    
    Dim Message2, Title2, variable2
    Message2 = "Enter a Column number"    ' Set prompt.
    Title2 = "InputBox Demo"    ' Set title.
    
    Dim Message3, Title3, variable3
    'Message3 = "Delete entire row if value in column " & variable2 & " = " ' Set prompt.
    Message3 = "Delete entire row if value in column  = " ' Set prompt.
    Title3 = "InputBox Demo"    ' Set title.
    
    

variable1 = Application.inputbox(Message1, Title1)
variable2 = Application.inputbox(Message2, Title2)
variable3 = Application.inputbox(Message3, Title3)

'MsgBox "Delete entire row if value in column" & variable2

'Select the usedRange
    ActiveSheet.UsedRange.Select
    
'Get the Number of the rows used
    NumRows = ActiveSheet.UsedRange.Rows.Count
    'Delete all rows ending with VARIABLE1 where value in Column 'VARIABLE2 is VARIABLE3

    'For iLine = NumRows To variable1 Step -1
    For iLine = NumRows To 1 Step -1
        If Range(variable2 & (iLine)).Value = variable3 Then
               Rows(iLine).EntireRow.Delete
        End If
   Next iLine
End Sub

EDIT: Added Code tags - Moderator