Excelのこととか色々

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

【Excel/VBA 03】Adjust Row Height and Column Width by One Click

Excel/English

※This is a re-write of the following article in English. 
 Also, this article would be simpler than the Japanese one.

www.tuna-kichi.com

 

 I will introduce a simple macro of adjusting rows and columns.

 It will be more convenient if you add it to the ribbon.

 

 

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

 

Automatically adjust row height and column width

Macro

Adjust row height 

****************************************
Sub AutoAdjustRowHeight

  Rows(Selection.Row).Autofit

End

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

 

 Adjust column width

****************************************
Sub AutoAdjustColumnWidth

  Columns(Selection.Column).Autofit

End

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

 

It should be easy.

Only one sentence.

 

 

I will explain it simply.

Rows・・・Designate rows

(Selection.Row)・・・Designating row which currently selected.

Autofit・・・Automatically adjust

 

Designate row number in the parentheses.

basically, a row number will be written there such as 1, 3, or 254.

 

Also, you can use "Selection.Row", as I wrote above.

That means you designate the row number which currently selected cell.

(likewise columns)

 

 

 

Add the macro on the Ribbon

1. Right-click on the Ribbon anywhere blank.

2. Click "Customize the Ribbon"

3. Click "Macro" from "Choose Command from"

4. Select the macro you just created

5. Click the place where you want to display it no the right pane

6. Click the "Add" button

7. Rename it

 

 

It should be something like this.

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

 

 

Please check a little more detail from the following article.

www.tuna-kichi.com

 

The size of the cells which you selected is automatically adjusted when you click the macro on the Ribbon.

 

 

Reduces mouse slippage and failure.

 

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

 

 This macro makes +10pt from the current size.

+10pt row height

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

Sub RowHeightPlus10pt()

  Rows(Selection.Row).RowHeight = Rows(Selection.Row).RowHeight + 10

End Sub

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

 

Meaning...

The row height of the selected cell = the row height of the selected cell +10pt

 

The difference is only "+10" between the left and right.

 

I manually resize the column width on my work, though the macro of adjusting column width is the same as row's.  

Just replace the row with the column.

 

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

Sub ColumnWidthPlus10pt()

  Columns(Selection.Column).ColumnWidth = Columns(Selection.Column).ColumnWidth + 10

End Sub

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

 

 

Combine them

 

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

Sub AutofitAndRowHeightPlus10()

  Rows(Selection.Row).AutoFit 

  Rows(Selection.Row).RowHeight = Rows(Selection.Row).RowHeight + 10

End Sub

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

 

Just combined them.

The row height will be +10pt after AutoFit is done.

 

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