Excel のこととか楽天とか いろいろ書いてみます・・・

【Excel/VBA 02...Insert Row/Column By One Click】

【Excel/VBA 02】

※Please correct my English if you find any mistakes.  Thank you.



Write macros of insert row and column by VBA on the Personal Macro Workbook, and then, set it on the ribbon that you can use it anytime by one click.





Open VBE

You can open VBE(Visual Basic Editor)from the [Developer] Tab.



Insert [Module] under [VBA Project (PERSONL.XLSB)]



Rename the [Name] from [Module1] to anything you want

but it should be easy to understand.


Write macros which I will show you later in here or in other articles in this book.

Write Macros

This is the macro of insert one row.

Sub Insert_One_Row





And insert one column.

Sub Insert_One_Column





The first and the last sentences are just telling the start and the end,

so the operation code is just one line.


How is that ?   Just one code.   It should not be difficult.



Indicate row number following [Rows].

Basicaly,a number will be written in the parenthesis

but you can write [Selection.Row] which

indicates the row number that currently selected cell

(same column, too)



Set the macro to the ribbon

Set the macro to the ribbon and you can use it anytime.


Right click any space on the ribbon and select [Customize the ribbon].



Click [New Tab] to add a tab and rename it.



Click [New Group] to add a group and rename it.



Then, add the macro that you created to the group.


① Select the group

② Select [Macros] from [Choose commands from]

③ Select [PERSONAL.XLSB!AddOneRow】

④ Click [Add] 

⑤ Check it is added



Rename the macro's name shorter or any name you want.


Also you can pick the icon from the list then click [OK].




Open [Macros]  tab after back to the Excel window and check it how it is.



 Now, you can add a row by one click anytime, any files.



How do you add a row?

Something like......

Click the row number in the left.

Right click.

And select [Insert].



You don't have to click one whole row if you use this macro.

You can use it like this.

The mouse pointer stays on this macro on the ribbon, and cell A1 is selected

Press down arrow key where you want to add by left hand.

Then click the macro by right hand.


Move the cursor by up/down arrow key and click the macro to add the row.


Select a row → Right clikc → Insert

This operation might be done about one second,

but I'm sure you are going to use faster way if you know it is done within one second, 

In my opinion, this reduces your stress.


Let's pass your work to Excel because it knows how to work if you order!