• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding Rows Macro

I have the following code. It's purpose is to allow users to insert any number of rows that they wish using an input box. However, when you insert, "2", the macro does nothing. Just wondering what simple bit of code I'm overlooking to make this work. It's blowing my mind right now that 1, 3 and any other number I input work without issue. 2!

[pre]
Code:
Sub Add()
Dim RowA As String
RowA = 0

RowN = InputBox("Enter the number of rows you would like to add", "Rows")
If RowN = vbCancel Or RowN = 0 Or RowN = "" Then Exit Sub

Set PrevCell = ActiveCell

Application.ScreenUpdating = False

Do

RowA = RowA + 1
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFormLeftOrAbove
If RowA = RowN Then GoTo Here

Loop

Here:
Range("M2").Select
Selection.AutoFill Destination:=Range("M2:M9999")

Range("N2").Select
Selection.AutoFill Destination:=Range("N2:N9999")

PrevCell.Select

Application.ScreenUpdating = True

End Sub
[/pre]
 
Hi, eldridge.michael!


It's just because 2 is the value of the built-in Excel constant vbCancel.


Maybe you want to give a look at this file, it does something almost equal:

http://chandoo.org/forums/topic/vba-excel-add-rows-per-number-of-checkboxes-on-userform


Regards!
 
SirJB7,


Thank you for your help. You hit the nail on the head. The default value for vbCancel is 2, so I simply removed the line of code:


Code:
RowN = vbCancel


Since the field was either blank or 0 the cancel button still functions and the macro exits without issue.


Thank you again for your help!
 
Hi, eldridge.michael!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!
 
Back
Top