マクロをどんな Excel ファイルにも実行できるように設定して、3秒かかる操作も1秒で完了。あといろいろ書いてみます・・・

【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!




PVアクセスランキング にほんブログ村 Excelのこととか色々 - にほんブログ村 にほんブログ村 子育てブログ 幼稚園児育児へ にほんブログ村 英語ブログ 初心者英語へ にほんブログ村 IT技術ブログ VBAへ