RangeオブジェクトにCellsプロパティを使う

あるセルを参照するのに
RangeプロパティとCellsプロパティの両方が使えます。

アクティブシートのセルA1の値を1にするのは
次の2通りでできます。
ActiveSheet.Range("A1") = 1
ActiveSheet.Cells(1, 1) = 1

Rangeプロパティを使うと
A1形式で指定しないといけないので
For文の中ではRangeプロパティは使いにくいです。

一方でCellsプロパティは
行と列を数字で指定できるので
For文の中で使いやすいです。

今までRangeプロパティとCellsプロパティは
上述の理由から使い分けをして来ました。

実は、CellsプロパティはRangeオブジェクトにも使えます。
参照：MSDNサイト内「Cells プロパティ」

例えば次のように使います。

Range("B2:D6").Cells(2, 2) = 2

上のコードはアクティブシートのセルC3の値を2にします。

この使い方ではCellsプロパティは
Rangeオブジェクトの範囲(上の図の赤枠内)で
左上のセルを基準に(2,2)の位置である
セルC3を相対的に指定します。

この使い方は結構便利です。

RangeオブジェクトにCellsプロパティを使用して
Rangeオブジェクトに含まれる各セルに
値を入れて行くのに使えます。

Rangeオブジェクトの行数、列数はそれぞれ
Range("B2:D6").Rows.Count
Range("B2:D6").Columns.Count
で取得して、
For文のインデックスに使用します。

コードはこちら

Sub macro180729a()
'Cellsプロパティの使い方
'Rangeオブジェクトに使用

    Dim i As Integer, j As Integer
    Dim Rng1 As Range
   
    Set Rng1 = Range("B2:D6")
   
    For i = 1 To Rng1.Rows.Count
        For j = 1 To Rng1.Columns.Count
            Rng1.Cells(i, j) = i & ", " & j
        Next j
    Next i
   
End Sub

実行結果：

Rangeオブジェクトの範囲"B2:D6"のセルに値が入力されました。

Automatically adjust height of merged cell

When you double-click between a line and a line in Excel
The height of the cell is automatically adjusted.

However, in a merged cell
Height can not be adjusted automatically.

Even when a merged cell is applied to
"Format Cells" - "Display Fully Wrap",
The height is not automatically adjusted.
In case for printing in this state, necessary parts will be missing.

I am making this possible with VBA.
I came up with an easy way using worksheets.

The Roughly flow of the code as follows

① Create a sheet "macro180724a" for working.
② Input the string in the merged cell
to the cell A1 not merged in the working sheet.
③ Make the width of cell A1 the same as
the width of the whole merged cell.
④ Automatically adjust the height in cell A1 and
Obtain cell height.
⑤ Set this height to the original merged cell.

Code here

Sub macro180724a()
'Automatically adjusts height of merged cell
'Before running this macro
'Select the range you want to adjust

    Application.ScreenUpdating = False
   
    Dim obj As Object
    Dim i As Integer
    Dim c As Integer
    Dim width1 As Single
    Dim rng1 As Range
    Set rng1 = Selection
   
    Dim sh1 As Worksheet
    Sheets.Add.Name = "macro180724a"
    Set sh1 = Sheets ("macro180724a")
    With sh1.Cells(1, 1)
        .WrapText = True
        .ShrinkToFit = False
        Rows(.Row).EntireRow.AutoFit
    End With
   
    For Each obj In rng1
        'Only merged cells
      If obj.MergeCells = True Then
            sh1.Range("A1") = obj.MergeArea.Cells(1, 1).Text
            For i = 1 To obj.MergeArea.Count
                If obj.MergeArea.Item(i).Column > c Then
                    width1 = width1 + obj.MergeArea.Item (i).ColumnWidth
                    c = obj.MergeArea.Item(i).Column
                Else
                    i = obj.MergeArea.Count
                End If
                If i = obj.MergeArea.Count Then
                    sh1.Cells(1, 1).ColumnWidth = width1
                    Rows(sh1.Cells(1, 1).Row).EntireRow.AutoFit
                    c = 0
                    width1 = 0
                End If
            Next i
           
            With obj
                .WrapText = True
                .ShrinkToFit = False
                .RowHeight = sh1.Cells(1, 1).RowHeight
            End With
           
        End If
    Next
   
    Application.DisplayAlerts = False
    sh1.Delete
    Application.DisplayAlerts = True
   
    Application.ScreenUpdating = True
End Sub

Execute the above code in the state of the image below

Result：

I tried the above code several times.
Please check the result
because it may be set to a height that is high by one line
depending on the input string or cell width.

結合したセルの高さを自動調整する

Excelで行と行の間をダブルクリックすると
セルの高さが自動調整されます。

しかし、結合したセルでは
高さの自動調整ができません。

結合したセルに
「セルの書式設定」-「折り返して全体を表示する」
が適用されているときでも
高さが自動調整されません。
この状態で印刷すると必要な部分が欠けてしまいます。

結合したセルの高さ調整をVBAでできるようにしていきます。
ワークシートを使った簡単な方法を思いつきました。

コードの流れは
ざっくり次の通り

①作業用シート「macro180724a」を作成する
②結合されたセル内の文字列を
　作業用シートの結合されていないセルA1に入力する。
③セルA1の幅を結合されたセル全体の幅と同じにする
④セルA1で高さ自動調整をして
　セルの高さを取得する
⑤この高さを元の結合されたセルに設定する。

コードはこちら

Sub macro180724a()
'結合したセルの高さ調整
'マクロを実行する前に
'調整したい 範囲を選択しておく

    Application.ScreenUpdating = False
   
    Dim obj As Object
    Dim i As Integer
    Dim c As Integer
    Dim width1 As Single
    Dim rng1 As Range
    Set rng1 = Selection
   
    Dim sh1 As Worksheet
    Sheets.Add.Name = "macro180724a"
    Set sh1 = Sheets ("macro180724a")
    With sh1.Cells(1, 1)
        .WrapText = True
        .ShrinkToFit = False
        Rows(.Row).EntireRow.AutoFit
    End With
   
    For Each obj In rng1
         'セルの結合あり
       If obj.MergeCells = True Then
            sh1.Range("A1") = obj.MergeArea.Cells(1, 1).Text
            For i = 1 To obj.MergeArea.Count
                If obj.MergeArea.Item(i).Column > c Then
                     width1 = width1 + obj.MergeArea.Item (i).ColumnWidth
                     c = obj.MergeArea.Item(i).Column
                 Else
                     i = obj.MergeArea.Count
                End If
                If i = obj.MergeArea.Count Then
                    sh1.Cells(1, 1).ColumnWidth = width1
                    Rows(sh1.Cells(1, 1).Row).EntireRow.AutoFit
                    c = 0
                     width1 = 0
                End If
            Next i
           
            With obj
                .WrapText = True
                 .ShrinkToFit = False
                .RowHeight = sh1.Cells(1, 1).RowHeight
            End With
            
        End If
    Next
   
    Application.DisplayAlerts = False
    sh1.Delete
    Application.DisplayAlerts = True
   
    Application.ScreenUpdating = True
End Sub

下の画像の状態で上記のコードを実行

実行結果：

上記コードを何回か試しましたが、
入力された文字列やセルの幅によっては
1行余分な高さに設定されることもあるので注意してください。

Display strings on the right side of cells

When entering a long character string in a cell in Excel
If nothing is entered in the right cell
Like the A1 cell of the image below
Characters are displayed on the right side.

In the image, the same character string is entered from A1 to A3 cell.
Even if nothing is entered in the right cell
Like the A2 cell
Characters may not be displayed on the right side.

This happens in the following cases.
· When cell formatting is checked
　"Show folded and show whole"
And
· When the cell height is not automatically adjusted

If the height of the cell is insufficient
The character string will not be displayed
and it will be cut in the middle.
It is a problem because characters are printed out in this state.

The A3 cell is in a state
the height of A2 cell is automatically adjusted.
The character string in that cell is
wrapped and displayed.

You want to display strings on the right side
like the A1 cell of the previous picture.
I will introduce macros that can be used in such cases.

Code here

Sub macro180708a()
'Display strings on the right side of cells
'Before running this macro
'Select the range you want to adjust

    Dim obj As Object
   
    For Each obj In Selection
        'No cell merge
       If obj.MergeCells = False Then
            With obj
                'No wrapping
                .WrapText = False
                'No shrink
                .ShrinkToFit = False
            End With
             'Automatic cell height adjustment
            Rows (obj.Row).EntireRow.AutoFit
        End If
    Next
   
End Sub

Execute the above code in the state of the image below

Result：

I noticed this time writing this article
When "display by folding" is set in the merged cell,
the cell height is adjusted to one line.
So in the above code
I targeted only unmerged cells.

セルに入力した文字をはみ出して表示させる

Excelでセルに長い文字列を入力した時
右側のセルに何も入力されていなければ
下の画像のA1セルのように
文字が右側にはみ出して表示されます。

A1からA3セルには同じ文字列が入力されています。
右側のセルに何も入力されていなくても
A2セルのように
文字がはみ出して表示されなくなることがあります。

これは、セルの書式設定で
「折り返して全体を表示する」に
チェックが入っている時、かつ
セルの高さが自動で調整されない時になります。

セルの高さが不足していて
文字列が表示されずに途中で切れてしまいます。
この状態で印刷すると文字が切れて印刷されるので問題です。

A2セルの高さを自動調整した状態がA3セルになります。
A3セルでは文字列が折り返して表示されています。

先ほどの画像のA1セルのように右側にはみ出して表示させたい。
こんな時に使えるマクロを作りたいと思います。

コードはこちら

Sub macro180708a()
'文字をはみ出して表示させる
'マクロを実行する前に
'調整したい 範囲を選択しておく

    Dim obj As Object
   
    For Each obj In Selection
        'セルの結合なし
       If obj.MergeCells = False Then
            With obj
                '折り返し て表示しない
                .WrapText = False
                '縮小して表示しない
                .ShrinkToFit = False
            End With
             ' セルの高さ自動調整
            Rows (obj.Row).EntireRow.AutoFit
        End If
    Next
   
End Sub

下の画像の状態で上記のコードを実行

実行結果：

今回の記事を書いていて気付いたのですが
結合したセルで「折り返して表示する」を設定している場合、
セルの高さ自動調整では1行分に調整されてしまいます。
ですので上記コードでは
結合していないセルのみ対象としました。