MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using a macro button to add however many lines I choose

Posted by Greg on August 08, 2001 7:12 AM

I need to have a something say, "How many lines do you want to enter?" Lets say I enter 4 then hit the command button, I need rows 2,3,4,5 to be blank with the current borders still intact, and everything that was in rows 2-5 to be in rows 6,7,8, and 9. Anyone know how to do this? Thanks a lot.


Posted by faster on August 08, 2001 8:01 AM

'this should do it

Sub AddRows()
On Error GoTo etrap
'number of rows
Dim MyNum
MyNum = InputBox("How many rows do you want to add?")

'data validation
If MyNum = "" Then
MsgBox "Integer required"
Exit Sub
ElseIf IsNumeric(MyNum) = False Then
MsgBox "Integer required"
Exit Sub
End If

'insert rows
Dim i
For i = 1 To MyNum
Next i
Exit Sub

'catch errors
MsgBox "should never get here"

end sub

Posted by Greg on August 08, 2001 8:11 AM


I really appreciate the help. Only problem is I am completely ignorant of Macros. Do i copy this and put into a command button? If I do then where do I enter the number of Rows I would like to have added. Thanks a lot.


Posted by faster on August 08, 2001 8:22 AM

Go into the Visual Basic editor
Select Tools/Macro/Visual Basic Editor

Inside the editor select

Paste the code in the module.

Back in Excel:
You can run the code by selecting Tools/Macro/Macros
this will display macros available. You can assign
a hot-key in this section.


you can customize your toolbar by adding a button
and assigning the macro to it.

It may appear complex, but it is pretty easy.
You should take some time to look around in the editor
when you can.

Posted by Greg on August 08, 2001 8:24 AM

NEVERMIND that last one; i kinda figured it out. 2 little questions though....

First of all thanks,

its working almost perfectly. Only problem is that on the macro it creates lines that are too high (height 21.00 instead of 12.75) and I would like the bottom line to have a double bar border. Is this stuff even possible. hanks for all of your help already and thanks for looking at all this stuff I have to do. I really appreciate it.