Auto number rows in the column C

isaacv22

New Member
Joined
Sep 30, 2021
Messages
48
Office Version
  1. 365
Platform
  1. Windows
Hello,

So i have the code below and I would like it to auto number the inserted rows below based on the value of the additonal tracks. the selected cell would be 1 and then the following 2-12.

Dim chk
Dim tracks As Long

' Exit if more than one cell selected
If Target.CountLarge > 1 Then Exit Sub

' Exit if selection not made to column C after row 3
If Target.Column <> 3 Or Target.Row < 3 Then Exit Sub

' Prompt user for how many tracks to insert
chk = MsgBox("Are there multiple tracks?", vbYesNo)
If chk = vbYes Then
On Error GoTo err_chk
tracks = InputBox("How many additional tracks are there (1-12)?")
On Error GoTo 0
If tracks >= 1 And tracks <= 12 Then
' Insert rows
Application.EnableEvents = False
Rows(Target.Row + 1 & ":" & Target.Row + tracks).Insert
Application.EnableEvents = True
Else
MsgBox "You have not entered in a valid number of tracks.", vbOKOnly, "ENTRY ERROR!"
End If

End If

Exit Sub

err_chk:
MsgBox "You have not entered a valid number.", vbOKOnly, "ENTRY ERROR!"
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

JGordon11

Well-known Member
Joined
Jan 18, 2021
Messages
553
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
try after your insert step

Selection.Resize(tracks, 1) = Application.Sequence(tracks)
 
Solution

Forum statistics

Threads
1,175,889
Messages
5,900,078
Members
434,817
Latest member
nbretscher13

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