Insert desired rows on condition basis

[Closed]
Report
Posts
2
Registration date
Tuesday September 24, 2013
Status
Member
Last seen
September 24, 2013
-
Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
-
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 reply

Posts
2783
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 3, 2021
468
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!