[[20230226180306]]　『列数がバラバラのセルの結合の仕方』（ky）　ページの最後に飛ぶ

『列数がバラバラのセルの結合の仕方』（ky）

お力をお貸しください。

G1のセルから住所1，住所2、住所3、住所4．．．のデータがいきなり入っているデータがあります。（便宜上、住所1，住所2　等で表記いたしましたが、下記のようにデータがいきなり入っています。）

1 | | 111111 | 1street |aaaaaaa |NewYork | NY | USA
2 | | 222222 | 2street |bbbbbb |London | |UK
3 | | 3333333 |3sterrt |cccccc |ddddddddd |indonesia |
4 | |4444 |hyogo |kobe | | |
5 | |55555 |5sterrt | | | |
・
・
・
・

これを,一つ前のF列に","でつなげてコピーしたいのです。
例えば、1行目だと、
111111,1street,aaaaaaa,NewYork,NY,USA
という最後の,は取り除いた形にしたいのです。
関数でコピーとも考えたのですが、ファイル数が多いのと、シートによって
バラバラで時間がかかるかと思い、下記のようなVBAを作ってみたのですが、
うまくいきません。

　Dim MR As Long
　Dim MC As Long
　Dim a As Range
　Dim b As String

　MR = Cells(Rows.Count, 7).End(xlUp).Row '最終行
　MC = Cells(7, Columns.Count).End(xlToLeft).Column '最終列
　Dim i As Integer

   For i = 1 To MR
      b = ""
      For Each a In Range(Cells(i, 7), Cells(i, MC))
          b = b & a.Text & ","
      Next a

      Cells(i, 6).Value = b
    Next i

どうやってもG列とH列の2つしかとってこず、H列が空欄でも","でつなげてしまい、最後の","も取れません。
111111,1street,　となります。
最終行の取得はうまくいっている（ように見える）のですが。

どこがおかしいのでしょうか？

できれば最後の","ととる方法もご教授いただければ幸いです。

先頭のデータG1から始まっているのは固定です。

よろしくお願いいたします。

＜使用 Excel：Excel2019、使用 OS：Windows10 ＞

excel2019ならTEXTJOIN関数を使えば簡単にできます。
複数シートでも、作業グループにして、絶対に使用しない列に

 =TEXTJOIN(",",TRUE,セル範囲)

として下にコピーすればすぐ終わると思います。
セル範囲は、開始列から数式を入れる列の手前まで設定すれば間違いないでしょう。
（フォーキー） 2023/02/26(日) 18:56:32

すみません、質問を読み間違えてました。
G列以降にデータがあって、F列に結合したデータを、ですね。
上の回答をF列に数式、セル範囲を「G列から全シート中一番右の列」に変更してください。
（フォーキー） 2023/02/26(日) 19:03:44

VBAならこんな感じ

 Sub test()
     Dim i As Long
     For i = 1 To ThisWorkbook.Worksheets.Count
         With Worksheets(i).Range("F1:F100") '実際の最終行に変更
             .Formula = "=TextJoin("","",True,G1:Z1)"  '実際の最終列に変更
             .Value = .Value
         End With
     Next
 End Sub
（フォーキー） 2023/02/26(日) 19:17:50

Option Explicit

Sub test()

    Dim MR As Long
    Dim b As String
    Dim temp As Long
    Dim i As Long, j As Long

    MR = Cells(Rows.Count, 7).End(xlUp).Row '最終行

    For i = 1 To MR
        b = ""
        temp = Cells(i, Columns.Count).End(xlToLeft).Column - 6
        For j = 7 To temp + 7
            b = b & Cells(i, j).Value & ","
        Next
        Cells(i, 6).Value = b
    Next

End Sub

（諸子頑鉄） 2023/02/26(日) 19:24:43

既に提示のある、TEXTJOINを使う方が良いと思います。
あえて御提示のコードを改善するなら以下の様な感じでしょうか。

    MR = Cells(Rows.Count, 7).End(xlUp).Row '最終行
    For i = 1 To MR
        MC = Cells(i, Columns.Count).End(xlToLeft).Column '最終列
        b = ""
        For Each a In Range(Cells(i, 7), Cells(i, MC))
            If Not a = "" Then
                b = b & "," & a.Text
            End If
        Next a
        Cells(i, 6).Value = Mid(b, 2)
    Next i

行毎に最終列を求めるのと、
カンマで区切る場合、例えば「,111111,1street,aaaaaaa・・・・」の様に、
先頭にカンマが付くようにして、MID関数で2文字めから取得するのが常套手段かと思います。
（すいとん） 2023/02/26(日) 20:38:33

修正しました。
（最後に不必要なカンマが付加されないように）

Option Explicit

Sub test()

    Dim MR As Long
    Dim b As String
    Dim temp As Long
    Dim i As Long, j As Long

    MR = Cells(Rows.Count, 7).End(xlUp).Row '最終行

    For i = 1 To MR
        b = ""
        temp = Cells(i, Columns.Count).End(xlToLeft).Column
        For j = 7 To temp
            If j <> temp Then
                b = b + Cells(i, j).Text & ","
            Else
                b = b + Cells(i, j).Text
            End If
        Next

        Cells(i, 6).Value = b
    Next

End Sub

（諸子頑鉄） 2023/02/27(月) 09:29:09

皆様ご回答ありがとうございます。

フォーキー様
TEXTJOIN関数というものができているのですね。
10年ぶりくらいにexcelをさわったので、concatenateしか思い浮かばず．．．
これ、全部繋げるのか．．．と泣きそうになっていました。

諸子頑鉄様
カンマが付加されない修正分までありがとうございます。
最後と最後じゃないのを分岐すればよかったんですね。

すいとん様
行ごとに最終列を求めるのはFor Nextの中で行うのですね。
MRの下に書いているので、大丈夫かと思っていました。
カンマ区切りの場合、先頭にカンマ．．．なるほど、そうすると最後のカンマはつかない。
分かりやすい説明ありがとうございます。

皆様、本当にありがとうございました。

（ky） 2023/02/27(月) 10:14:00

コメント返信：

[ 一覧(最新更新順) ]

[[20230226180306]] 『列数がバラバラのセルの結合の仕方』（ky） ページの最後に飛ぶ

[[20230226180306]]　『列数がバラバラのセルの結合の仕方』（ky）　ページの最後に飛ぶ