Try the attached. When the input box comes up put in the scenario number (only the number and the scenario's go in the same order as in your first post).
I have put a copy of the code below and when you want to change the text you change the text in red in the code.
I must admit though if it was me I'd still use a userform to enter the text and scenario number in one go from the spreadsheet but that's just me.
Sub TestIt()
Dim F1x As Range, F2x As Range, ws As Worksheet, sarray
Dim x As String, y As String, number As Integer
sarray = Array("SheetA", "SheetB", "SheetC")
number = InputBox("Enter scenario number :")
For Each ws In ActiveWorkbook.Worksheets
If IsError(Application.Match(ws.Name, sarray, 0)) Then
x = "first"
y = "last"
Set F1x = ws.Columns("A").Find(x, LookIn:=xlValues, lookat:=xlPart)
Set F2x = ws.Columns("A").Find(y, LookIn:=xlValues, lookat:=xlPart)
Select Case number
Case Is = 1
If Not F1x Is Nothing And Not F2x Is Nothing Then ws.Rows(F1x.Row & ":" & F2x.Row).Delete
Case Is = 2
If Not F1x Is Nothing And Not F2x Is Nothing Then ws.Rows(F1x.Row & ":" & F2x.Row - 1).Delete
Case Is = 3
If Not F1x Is Nothing And Not F2x Is Nothing Then ws.Rows(F1x.Row & ":" & F2x.Row + 1).Delete
Case Is = 4
If Not F1x Is Nothing And Not F2x Is Nothing Then ws.Rows(F1x.Row & ":" & F2x.Row + 2).Delete
End Select
End If
Next ws
End Sub
Bookmarks