【VBA】エクセルで最後の不要な改行コードを削除するコードについて

Excel（office365）を使っている者です。
以下はクリップボードに値のみをコピーするコードですが，範囲内に空白行があった場合，クリップボードには余計な改行が入ってしまいます。
最後の余計な改行を消したいため，ネットで調べて，For Each ary In Selection～nextを入れたのですが，型が一致しないということでエラーになってしまいます。どうすれば余計な改行を削除できるか，ご教示願います。よろしくお願いいたします。

---------------------------------------------------------------------------

Sub 値のみコピー()

Dim ary As Variant
ary = Range("A10:J30").Value
Dim myDO As New DataObject

Dim i As Long, j As Long
Dim strBuf As String
For i = 1 To UBound(ary, 1)
For j = 1 To UBound(ary, 2)
strBuf = strBuf & ary(i, j)
Next

strBuf = strBuf & vbCrLf '改行コードを付加

For Each ary In Selection
If Len(ary.Value) > 0 Then
Do While Right(ary.Value, 1) = vbLf
ary.Value = Left(ary.Value, Len(ary.Value) - 1)
Loop
End If
Next

Next

myDO.SetText strBuf
myDO.PutInClipboard
Set myDO = Nothing
End Sub

No.1 ベストアンサー 回答者： goomania 回答日時： 2023/09/09 18:52

前提条件として、A10:J30のセルの値を、1行ごとに区切り文字なしに連結して改行し、まとめてクリップボードに送るコードです。

以下の2点を実現するために、コードを修正してみました。
(1)範囲内に空白行があった場合、これを無視して、改行コードを入れない
(2)データの最後の改行を入れない


Sub send_clipboard()
Dim ary As Variant
Dim myDO As New DataObject
Dim i As Long, j As Long
Dim LineBuf As String
Dim strBuf As String

ary = Range("A10:J30").Value
For i = 1 To UBound(ary, 1)
For j = 1 To UBound(ary, 2)
LineBuf = LineBuf & ary(i, j)
Next
If (Len(LineBuf) > 0) And (i < UBound(ary, 1)) Then LineBuf = LineBuf & vbCrLf '空白行でなく、最終行でもないとき改行コードを付加
strBuf = strBuf & LineBuf
LineBuf = ""
Next
Set myDO = New DataObject
myDO.SetText strBuf
myDO.PutInClipboard
Set myDO = Nothing
End Sub

ご質問者の言う
>最後の余計な改行を消したい
という意味が「空白行の最後」なのか「データの最後」なのか曖昧たったので、「データの最後」も含むと解釈しましたが、「空白行の最後」のみという意味の場合、
If (Len(LineBuf) > 0) And (i < UBound(ary, 1)) Then ･･････
というコードを
If (Len(LineBuf) > 0) Then ･･････
に修正してください。

この回答へのお礼

誠にありがとうございます。
お礼が遅くなり申し訳ありません。
動作を確認しましたが，希望どおりの動きでした。

実はコードについて調べても理解できない点があるため，教えていただけると幸いです。
---------------------------------
ary = Range("A10:J30").Value
For i = 1 To UBound(ary, 1)
For j = 1 To UBound(ary, 2)
LineBuf = LineBuf & ary(i, j)
---------------------------------
の部分ですが，UBound(ary, 1)では，何が戻り値になりますか？
また，LineBuf = LineBuf & ary(i, j)とはどういう処理でしょうか。

お礼日時：2023/09/13 16:35

No.3 回答者： goomania 回答日時： 2023/09/15 12:35

No.1,2です。

>strBufの変数には何の意味があるのでしょうか。
strBuf変数も、もともと、ご質問者のVBAに登場する変数であり、その意味を変更していません。
ご質問者のVBAでは、strBufは全てのセルを結合して一纏めにしたものですが、私のコードでもstrBuf変数は一行を一纏めにしたLineBuf変数を繋ぎあわせて複数行のデータに纏めたものです。つまり、結果としてすべのセルを一目纏めにしたものであり、その意味は変わっていません。
但し、私のコードでは、一行ごとに改行の要否判定を行っていますので改行コードの部分のみ違いが発生することになります。
方法論として「纏めてから改行コードを排除する」という方法ではなく「纏める前に改行の要否を判断する」という方法に変えただけです。

この回答へのお礼

詳しく教えていただきありがとうございます。
なんとか理解できたように思います。

お礼日時：2023/09/19 17:57

No.2 回答者： goomania 回答日時： 2023/09/13 18:28

No.1です。

>UBound(ary, 1)では，何が戻り値になりますか？
aryにA10:J30のセル範囲を代入しているので、aryは行数21、列数10の2次元配列になります。
上記のとおり行数21ですので、行数である21が戻り値になります。
とはいえ、For i = 1 To UBound(ary, 1)は、もともとご質問者のVBAにもあるコードです。意味もわからずに使っていたということでしょうか?

>LineBuf = LineBuf & ary(i, j)とはどういう処理でしょうか。
上記説明のとおり、aryにA10:J30のセル範囲を代入しているのでary(i, j)は範囲内のセルの値を意味します。
iを固定して、jを進めれば、同じ行のセルの値を順次取得できます。
取得した値を次々とLineBufに&結合していけば一行分の結合データが出来上がります。

つまり、LineBufは一行分の結合データを作成するための変数です。
一行分の結合データが作成されたら、この長さをチェックして「長さ>0」のときだけ改行するようにすれば、空白セルでは改行されないことになります。

この回答へのお礼

丁寧に教えていただき，ありがとうございます。
すみません。もう一点教えていただけると幸いです。

strBufの変数には何の意味があるのでしょうか。
確かに，書いていただいたコードを以下のように，strBufをなくして走らせると何もクリップボードに入らなくなってしまいますが。


Sub send_clipboard()
Dim ary As Variant
Dim myDO As New DataObject
Dim i As Long, j As Long
Dim LineBuf As String

ary = Range("A10:J30").Value
For i = 1 To UBound(ary, 1)
For j = 1 To UBound(ary, 2)
LineBuf = LineBuf & ary(i, j)
Next
If (Len(LineBuf) > 0) And (i < UBound(ary, 1)) Then LineBuf = LineBuf & vbCrLf '空白行でなく、最終行でもないとき改行コードを付加

LineBuf = ""
Next
Set myDO = New DataObject
myDO.SetText LineBuf
myDO.PutInClipboard
Set myDO = Nothing
End Sub

お礼日時：2023/09/15 11:45