Macro to update #s based on amount selected

[tbakbradley]

I have a Macro that will bring up a box to ask the user to input the number of circuits required. I have some validation in the vba so that the user can only select a number between 1 and 50. Once that number has been entered by Sales and verified, the Number of Circuits are populated on a cell on the 'Cover' sheet, then the "Data" Sheet begins deleting rows so that only the number of rows the user requires is listed. FYI, there are two rows and a blank for each "Circuit" listed.

That all works fine. However, on the "Data" Worksheet, the default is 50 Locations. They are numbered as Circuits #1 - Circuit #50 where Circuit #1 spans A5-A6 (merged), Circuit #2 spans A8-A9, Circuit #3 spans A11-A12 and so on to Circuit #50. However, my Macro select Rows 10:12 to begin deleting based on the number Sales enters. Again, that all works fine, but what happens is my Circuits are then labeled: Circuit #1, Circuit #2, Circuit #49 and Circuit #50 if 4 Circuits are chosen. Anyone know a way I can have the Circuit #s in order based on the number they choose?

Sub BuildEPL()
'This section provides Sales with a Message Box to enter the number of Circuits
circuits = InputBox("Enter Total number of Circuits (not circuits) for Inquiry Request.")
Do While circuits = "" Or Not CStr(Val(circuits)) = circuits Or Val(circuits) > 50 Or Val(circuits) < 1
MsgBox "You must specify a (Number) between 1 and 50. If more than 50 circuits are required, please add manually and alert OCNE."
circuits = InputBox("Enter Total number of circuits for Inquiry Request.")
Loop
Ans = MsgBox("The number of circuits you entered is above, is this correct? Please note that if you enter a number, and it's not correct, Sales must Insert or Delete the appropriate rows as this function cannot be run again", vbYesNo, circuits)
Select Case Ans
Case vbNo
Do While Ans = vbNo
circuits = InputBox("Enter Total number of Circuits for Inquiry Request.")
Do While circuits = "" Or Not CStr(Val(circuits)) = circuits Or Val(circuits) > 50 Or Val(circuits) < 1
MsgBox "You must specify a (Number) between 1 and 50. If more than 50 circuits are required, please add manually and alert OCNE."
circuits = InputBox("Enter Total number of Circuits for Inquiry Request.")
Loop
Ans = MsgBox("The number of circuits you entered is above, is this correct? Please note that if you enter a number, and it's not correct, Sales must Insert or Delete the appropriate rows as this function cannot be run again", vbYesNo, circuits)
Loop
Case vbYes
End Select


'This section populates the number of Circuits on the Cover page so that number can be used for other calculations if needed on the spreadsheet
Sheets("Cover").Select
Range("A25").Value = "Number of Circuits Entered"
Range("D25").Value = circuits

'This section Unhides the EPL_VzON Locations Worksheet
Sheets("EPL_VzON Locations").Visible = True

'This section builds the EPL_VzON Locations Worksheet Circuits. Worksheet default is 50 locations and will delete unwanted circuits based on number of circuits selected by Sales
Sheets("EPL_VzON Locations").Select
NumbRowsa = 50 - circuits
For Counter = 1 To NumbRowsa
Rows("10:12").Select
Range("B11").Activate
Selection.Delete Shift:=x1Up
Next Counter

 

[tbakbradley]

One minor change to the above. There will never be less than (2) Circuits and I need to change the code so that it only accepts numbers 2-50.

 

[AlphaFrog]

Min input: 2

   [COLOR="Green"] 'This section provides Sales with a Message Box to enter the number of Circuits[/COLOR]
    circuits = InputBox("Enter Total number of Circuits (not circuits) for Inquiry Request.")
    Do While circuits = "" Or Not CStr(Val(circuits)) = circuits Or Val(circuits) > 50 Or Val(circuits) < [COLOR="Red"]2[/COLOR]

Delete circuits rows.

Change this...
'This section builds the EPL_VzON Locations Worksheet Circuits. Worksheet default is 50 locations and will delete unwanted circuits based on number of circuits selected by Sales
Sheets("EPL_VzON Locations").Select
NumbRowsa = 50 - circuits
For Counter = 1 To NumbRowsa
Rows("10:12").Select
Range("B11").Activate
Selection.Delete Shift:=x1Up
Next Counter

To this (not tested)...

[COLOR="Green"]    'This section builds the EPL_VzON Locations Worksheet Circuits. Worksheet default is 50 locations and will delete unwanted circuits based on number of circuits selected by Sales[/COLOR]
    Sheets("EPL_VzON Locations").Select
    If circuits < 50 Then
        Rows(4 + (circuits * 3)).Resize((50 - circuits) * 3).Delete
    End If

 

[tbakbradley]

Incredibe. This works PERFECTLY and the deletion of the rows are even smoother.

Thank you!!!

 

[AlphaFrog]

You're welcome. Glad it worked out and thanks for the feedback.