Ruby & Excel: Inserting and Deleting Rows and Columns

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!