View original (French)

Insert multiple lines with a macro

DADY07 Posted messages 8 Status Membre -  
 Thorgal28 -
Hello, could someone help me!
I would like to insert 10 rows at once using a macro
this macro inserts a single row but how can I make it understand that I need 10 without having to run this macro 10 times

Rows("5:5").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Thank you in advanceConfiguration: Windows XP
Internet Explorer 7.0

2 réponses

Réponse 1 / 2
eriiic Posted messages 24581 Registration date   Status Contributeur Last intervention   7 281
 
Good evening,

By pointing to the beach, and the .select is unnecessary:
Rows("5:14").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
or even shorter if it's the default values:
Rows("5:14").Insert

eric
3
Ankhsuamon Posted messages 50 Status Membre 2
 
Hello Eriiic,

Simplicity... There's nothing better...

Thank you
--
C'est incroyable comme il est plus facile pour une équipe de travailler ensemble, quand personne n'a la moindre idée de où elle va.
0
Thorgal28
 
Hello Anksuamon,

I confirm that Eriic's "simple" solution works well.

Sub insertion_10_lignes()

'Inserting 10 rows starting from row 5
Rows("5:15").Insert 'Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
MsgBox "Insertion of 10 rows completed!", vbInformation

End Sub

Note:
Please be careful not to keep your data in "Table" format in Excel (I had this problem at first!).
If that's the case, convert your data back to normal format: Excel Menu>Design>Convert to Range
Then re-run the macro "insertion_10_lignes".
0