Excelのこととか色々

マクロをどんな 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.

f:id:tuna-kichi:20200223230814p:plain

  

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.

f:id:tuna-kichi:20200329214015p:plain

 

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

f:id:tuna-kichi:20200329214332p:plain



 

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

but it should be easy to understand.

f:id:tuna-kichi:20200329215247p:plain



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

  Rows(Selection.Row).Insert

End

****************************************

 

And insert one column.

****************************************
Sub Insert_One_Column

  Columns(Selection.Column).Insert

End

****************************************

 

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].

f:id:tuna-kichi:20200405163059p:plain

 

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

f:id:tuna-kichi:20200405163407p:plain

 

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

f:id:tuna-kichi:20200405163729p:plain

 

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

 f:id:tuna-kichi:20200405164355p:plain

① 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.

f:id:tuna-kichi:20200405164930p:plain



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

 

f:id:tuna-kichi:20200405165133p:plain

 

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

f:id:tuna-kichi:20200405165223p:plain

 

 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!

 

 _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/