Add row before executing the Command Button Code

Question

Good Day,

I have this button that generate unique ID, but before executing that I want a new window that will ask the user how many rows that will add in "A20", then the code for generating ID will execute after adding an specific rows.

Here is the code of generating ID:

Private Sub CommandButton1_Click()
Dim x As String, rcell As Range, y As String, z As Long


x = "RFP" & Format(Now, "mmddyyhhmmss")
y = Left(x, 10)
z = Right(x, 2)

For Each rcell In Range("D20:D" & Range("D" & Rows.Count).End(3).Row)
If Cells(rcell.Row, 4) = "" Then Cells(rcell.Row, 4) = y & z
z = z + 1
Next rcell


End Sub

Thank you in advance any inputs are highly appreciated

dwirony · Accepted Answer · 2017-08-02 14:58:29Z

1

Something like this? Not sure what you meant by adding in "A20", I assumed you mean you're adding rows to row 20?

myvalue = Inputbox("How many rows should be added?")

For i = 1 to myvalue
    Range("A20").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next i

edited Aug 2, 2017 at 14:58

answered Aug 2, 2017 at 14:40

dwirony

5,4604 gold badges26 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

N323100 Over a year ago

Thank you for the inputs but I want only the cell A20 where to add the new cell not the entire A20..B20..C20.. sorry if my question is confusing..

dwirony Over a year ago

@N323100 Fixed it to just add new cells at A20, try it now -

Collectives™ on Stack Overflow

Add row before executing the Command Button Code

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related