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

jmckeone

Well-known Member
Joined
Jun 3, 2006
Messages
550
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.

Code:
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

kendavid

New Member
Joined
Oct 19, 2006
Messages
1
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.


Code:
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
 

Forum statistics

Threads
1,136,623
Messages
5,676,857
Members
419,656
Latest member
lironprofit

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
Top