A reader has asked how to insert a row into a worksheet.
To insert a row, call the Insert method on the Row object where you wish to insert a new row. So this...
worksheet.Rows(2).Insert
...will move row 2 down one row and insert a new row above it.
You can insert multiple rows by passing the Insert method a colon-separated string, so...
worksheet.Rows('2:5').Insert
...inserts 4 new rows and moves rows 2 to 5 down. Note that this is a string and must therefore be quoted.
Similarly, to insert a new column, call the Insert method on the Column object where you wish to insert a new column. So either of these...
worksheet.Columns(2).Insert
worksheet.Columns('B').Insert
...will move column B/2 over one column (to the right) and insert a new column B.
This...
worksheet.Columns('2:5').Insert
worksheet.Columns('B:E').Insert
...inserts 4 new columns and moves columns 2 to 5 to the right. Note that this is a string and must therefore be quoted.
Deleting Rows and Columns works the same way, using the Delete method...
worksheet.Rows(2).Delete
worksheet.Rows('2:5').Delete
worksheet.Columns(2).Delete
worksheet.Columns('B').Delete
worksheet.Columns('C:E').Delete
Keep in mind that after inserting or deleting rows, your row (or column) indexes change -- when you delete Rows(2), Rows(3) becomes Rows(2), etc.
That's all for now. Got a question or suggestion? Post a comment or send me email.
Thanks for stopping by!
1 comment:
Your blog has some excellent Ruby and tutorials, so thank you for that!
I'm having trouble, however, getting it to work. I posted http://stackoverflow.com/questions/6754063/unable-to-save-excel-file-after-modification-using-ruby-win32ole but though I would share it here, too, in case anyone had any ideas. Thanks!
Post a Comment