Insert desired rows on condition basis

Closed
aijaz_dews Posts 2 Registration date Tuesday September 24, 2013 Status Member Last seen September 24, 2013 - Sep 24, 2013 at 01:44 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Sep 24, 2013 at 11:47 AM
Sir.

I am new to this website. I want to learn/know to how to insert desired number of rows when any condition met.

For example:

I have a Spread sheet as
Col1 (S/No), Col2(Rank), Col3(name)


Now my question is if Col2=SP or DYSP or Inspr or SI or ASI, or CT, or SPO................... it should prompt me to how may rows to add................ Thnx in advance

1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 24, 2013 at 11:47 AM
Welcome to Kioskea Aijaz!

See if this code meets your requirements:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x, y As Integer
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "SP" _
Or Target.Value = "DYSP" _
Or Target.Value = "Inspr" Then 'add as much values as desired
x = InputBox("How many rows would you like to insert")
y = 0
Do
Target.Insert
y = y + 1
Loop Until x = y
End If
End Sub

Best regards,
Trowa
0