VBA 文字列の置換の除外ルールの設定方法

Question

こんにちは。
エクセルで、[貼り付け用シート]に張り付けたダウンロードデータを
[計算用シート]で文字列を入れたものを[保存用シート]に張り付けて、CSVで保存するVBAを作っています。

[貼り付け用シート]

コントロールカラム	商品管理番号	商品名
	item-01	送料無料 愛媛県産 みかん 蜜柑 10kg
	item-02	送料無料 愛媛県産 みかん 蜜柑 15kg
	item-03	送料無料 愛媛県産 みかん 蜜柑 20kg

↓

[計算用シート]

コントロールカラム	商品管理番号	商品名
u	item-01	【本日ポイント2倍】送料無料 愛媛県産 みかん 蜜柑 10kg
u	item-02	【本日ポイント2倍】送料無料 愛媛県産 みかん 蜜柑 15kg
u	item-03	【本日ポイント2倍】送料無料 愛媛県産 みかん 蜜柑 20kg

データは3000件に満たないものもあるので、不要データを削除したり、
ここまでは前任者のソースを切り貼りしてなんとか作成できたのですが、
「C列」の「商品名」の部分に半角スペースが多数入っているので同時に置換されてしまい困っています。
同じような動作をさせつつ、C行は除外する方法などありましたら教えていただきたいです。
よろしくお願いいたします。

▼▼▼

Sub マクロ1()
Sheets("計算用シート").Select
Range("A1", "C3000").Copy
Sheets("保存用シート").Select
Range("A1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False



Sheets("保存用シート").Select　
  Dim msg As String
  Dim col_num As Long
  Dim row_num As Long
  Dim row_end As Long
  Dim i As Long


  col_num = ActiveCell.Column
  row_num = ActiveCell.Row

  row_end = Cells(Rows.Count, col_num).End(xlUp).Row

  For i = row_end To row_num Step -1
    If Len(Cells(i, col_num).Value) = 0 Then
      Cells(i, col_num).Delete xlShiftUp
    End If
  Next i
  
  
  Sheets("保存用シート").Select

  Dim h As Range
  Dim buf As String
  Dim FileN As String

  FileN = Application.GetSaveAsFilename( _
    InitialFileName:="\CSV_" & Format(Now(), "yyyymmddhhmm") & ".csv", _
    FileFilter:="ＣＳＶ ファイル (*.csv), *.csv")
  Open FileN For Output As #1

  With Application
  For Each h In Range("A1:A" & Range("A65536").End(xlUp).Row)
    buf = Join(.Transpose(.Transpose(Range(h, Cells(h.Row, "IV")).Value)), " ")
    buf = .Trim(buf)
    buf = Replace(buf, " ", ",")
    Print #1, buf
  Next
  End With
  
End Sub

Answer 1

ほとんど変更したくないのであれば一例として。
※どこにも「_」がない場合

With Application
    For Each h In Range("A1:A" & Range("A65536").End(xlUp).Row)
        buf = Join(.Transpose(.Transpose(Range(h, Cells(h.Row, "IV")).Value)), "_")
        buf = .Trim(buf)
        buf = Replace(buf, "_", ",")
        Print #1, buf
    Next
End With

その場しのぎで根本的解決ではありませんが、
勉強していくうちに改善されるのも良いと思いました。

Comments

[beambeam]

なるほど、こういう考え方もあるのですね！答えは同じなのにいろいろな書き方があって、とても勉強になりました。ありがとうございます。

Answer 2

3列だけなら単純に & で繋げはいいのでは。

    With Application
        For Each h In Range("A1:A" & Range("A65536").End(xlUp).Row)
            buf = h.Value & "," & h.Offset(0, 1).Value & "," & h.Offset(0, 2).Value
            Print #1, buf
        Next
    End With

Join と Transpose を使うなら、

    With Application
        For Each h In Range("A1:A" & Range("A65536").End(xlUp).Row)
            buf = Join(.Transpose(.Transpose(h.Resize(1, 3).Value)), ",")
            Print #1, buf
        Next
    End With

Comments

[beambeam]

２つも回答をいただいてありがとうございます。
Transpose　については初めて聞いた言葉なので、もう少し勉強してみたいと思います。

Answer 3

なんで折角Joinしているのに、その場でカンマで結合しないんでしょうか。
列がIVまでと多めに取っているようなので、行ごとに列数が違うのでしょうか。
という前提で必要な列数分のみをJoinで結合すればよいと思いますので、以下でいかがでしょうか。
IV列がMAXと考え、その次のIW列から左方向にある最後の列までを結合します。

For Each h In Range("A1:A" & Range("A65536").End(xlUp).Row)
    buf = Join(.Transpose(.Transpose(Range(h, Cells(1, "IW").End(xlToLeft)))), ",")
    Print #1, buf
Next

Comments

[radames1000]

列がIVで行が65536なので昔のExcelの最大をとっているように見えますね。
作られた前任者の意図としては全範囲なのかもしれません。

[beambeam]

A65536の意味が今までわからなかったのですが、最大値ということだったのですね。
行ごとに列数が違うわけではないのですが、目からうろこでした。BAとさせてください。
めげずにもう少し勉強してみようと思います！ありがとうございました