こんにちは いつもお世話になっています

質問

こんにちは　いつもお世話になっています
エクセル2003を使っています。
特定の文字列以降の文字列を（特定文字列自身も含めて）削除する処理に関して質問します。
特定の文字列は二つあり、【＠】と【変化】です（【　】も含みます）。
この二つの文字列はバラバラに出てきます。一方だけがでてくるセル、両方出てくるセル、両方出てこないセル、があるという意味です。これらはB列だけにあります。
　現在のやり方は
　「*」を各特定文字の後ろに付けて、置換機能で削除しています（「【＠】*」、「【変化】*」みたいに）しかし、二つの文字列に対して別々にやるようなので、記録マクロで一括で処理しています。
　ここで質問なのですが
　置換後の文字列は空白にしているのですが、スペースが残ってしまいます。つまり、処理後にエクセルの書式メニューから「行」-「自動調整」をしてもスペースをつめられない状態です。どうすればスペースをつめられるでしょうか。
　あるいは、置換でなく、適当なマクロを紹介していただけると助かります。
　よろしくお願いします。

回答No.10

DOUGLAS_ · ベストアンサー

#8 DOUGLAS_ です。

＞置換した部分を左クリックしたまま
＞カーソルで最終行まで選択できてしまう部分のこと
＞目で見る限りでは空白なのに、黒く反転表示される
　#9 xls88 さんがお書きのように「改行文字」が挿入された状態かと存じます。

　お示しの「記録マクロ」を

Sub Macro1()
Cells.Replace What:=Chr(10) & "【＠】*", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("L5").Select
Cells.Replace What:=Chr(10) & "【変化】*", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Cells.Replace What:="【＠】*", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("L5").Select
Cells.Replace What:="【変化】*", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

のように書き直して、指定文字列の前に「改行文字」が入っている場合を先に置換しておくことで、問題が回避できるかと存じます。

　ひょっとしたら、「Chr(10)」（vbLf、ライン フィード文字）以外にも、「目で見る限りでは空白」の文字というのがあるかも知れませんので、その数だけ「Chr(※)」を含む ステートメント の部分を付け加える必要があるかも知れません。

　「Chr(10)」以外の場合については、例えば、「目で見る限りでは空白なのに、黒く反転表示される」セル を A1 とすると、
=CODE(A1)
という式で、A1セル 内の 文字コード が取得できますので、その数字を「Chr(数字)」のように書いていけばＯＫです。

お礼

DOUGLAS　様　ありがとうございました。
お蔭様で解決しました。
改行文字とかChr関数とか勉強しなければなりませんが、とりあえず作業が前に進みます。
私の言葉足らずを補ってご理解頂き助かりました。
簡単で恐縮ですが、お礼申し上げます。

回答No.9

xls88

＞回答番号：No.3 この回答へのお礼
特定文字（【＠】【変化】）のところで
Alt＋Enterでセル内改行されているのでは？

セル範囲は限定した方が無難です。
Cellsだとシート全体のセルが処理対象になります。
下記で試してみてください。
とりあえず、全て特定文字の処で改行されている場合です。

Sub test1()
With Range("B1:B10")
.Replace What:=Chr(10) & "【*】*", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With
End Sub

あるいは
Sub test2()
With Range("B1:B10")
.Replace What:=Chr(10) & "【＠】*", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
.Replace What:=Chr(10) & "【変化】*", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End With
End Sub

お礼

xls88　様　ありがとうございました。
重ね重ね言葉足らずで申し訳ありません。
PDICという辞書からのコピーしたデータなのですが、改行文字に気がつきませんでした。
特定文字の出現の仕方が不規則なせいか、せっかく教えていただいたコードがうまく働くところと、残ってしまうところとがありました。私の使い方が悪いのかもしれません。このコードでまた勉強させていただきます。
簡単で恐縮ですが、お礼申し上げます。

回答No.8

DOUGLAS_

１）置換後の文字列は空白にしているのですが、スペースが残ってしまいます。
２）つまり、処理後にエクセルの書式メニューから「行」-「自動調整」をしてもスペースをつめられない状態です。
３）どうすればスペースをつめられるでしょうか。

　(２) の頭に「つまり」と書かれていますが、(１) と (２) とが同義とは思えません。

　(１) に書かれている「スペース」というのは、もちろん「全角スペース」とか「半角スペース」というような「スペース」ですよね。

　(２)・(３) に書かれている「スペース」というのは、どういう意味でしょうか？

　「スペースをつめられない状態」とお書きですが、[書式(O)] - [行(R)] - [自動調整(A)] を実行しても、選択した範囲の セル 内の内容に応じて、行の高さが自動的に調節されるだけです。

　具体的には、[書式(O)] - [行(R)] - [自動調整(A)] を実行した場合には、「セル の内容が１行内に納まる セル」と「何も入力されていない 空白セル」とは同じ高さ（実際には、標準フォント の約1.1倍の高さ：http://homepage2.nifty.com/zaco/xlgrd/page01.html#3）に調節されるはずです。

　もし（３）にお書きの意味が、下記のような意味合いでしたら、補足願います。
・空白セル のある行の高さを「0」にする
・空白セル のある列の幅を「0」にする

お礼

DOUGLAS 様　ありがとうございました。
言葉足らずでごめんなさい。
置換した部分を左クリックしたままカーソルで最終行まで選択できてしまう部分のことを言いたかったのです。目で見る限りでは空白なのに、黒く反転表示されるのがわからないんです。
これも表現がおかしいかもしれませんが、ご理解いただけるとありがたいです。

回答No.7

KURUMITO

例えばＡ列のＡ1セルから下方にデータがあるとします。
Ｂ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",IF(AND(COUNTIF(A1,"*【＠】*")>0,COUNTIF(A1,"*【変化】*")>0),IF(FIND("【＠】",A1)>FIND("【変化】",A1),LEFT(A1,FIND("【変化】",A1)-1),LEFT(A1,FIND("【＠】",A1)-1)),IF(COUNTIF(A1,"*【＠】*")>0,LEFT(A1,FIND("【＠】",A1)-1),IF(COUNTIF(A1,"*【変化】*")>0,LEFT(A1,FIND("【変化】",A1)-1),A1))))

Ｃ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(B1<>"",COUNTIF(B$1:B1,"?*"),"")

Ｅ列に空白の行を除いた答えの表を表示させるとしてＥ1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ROW(A1)>MAX(C:C),"",INDEX(B:B,MATCH(ROW(A1),C:C,0)))

お礼

KURUMITO　様　ありがとうございました。
詳しく教えていただいて助かりました。
とても勉強になりました。
簡単で恐縮ですが、お礼申し上げます。

回答No.6

tom04

No.4・5です！
ごめんなさい。
ダブって投稿してしまいました。

どちらかは無視してください。m(__)m

お礼

tom04　様　ありがとうございました。
せっかく作っていただいたコードなんですが「型が一致しません」というエラーがでました。私の作動のさせ方が間違っているのかもしれません。
簡単で恐縮ですが、お礼申し上げます。

回答No.5

tom04

こんにちは！
無理やりって感じの方法です。
Sheet上のセルに空白が残っているとしての方法です。

↓のコードをSheet見出し上で右クリック、　→　コードの表示　を選択し
コピー＆ペーストしてマクロを実行してみてください。

Sub test()
Dim i, j, k As Long
Dim str, buf As String
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To Cells(i, Columns.Count).End(xlToLeft).Column
For k = 1 To Len(Cells(i, j))
str = Mid(Cells(i, j), k, 1)
'↓のIF文内　前者のスペースは「半角スペース」・後者のスペースは「全角スペース」で入力
If str = " " Or str = "　" Then
str = "" '←続けて""を入力
End If
buf = buf & str
Next k
Cells(i, j) = buf
buf = ""
Next j
Next i
End Sub

以上、お役に立てばよいのですが・・・m(__)m

回答No.4

tom04

こんにちは！
お役に立てるかどうか判りませんが・・・

Sheet上には空白が残っている状態でのコードです

Sheet見出し上で右クリック　→　コードの表示　を選択し
↓のコードをコピー＆ペーストしてマクロを実行してみてください。

Sub test()
Dim i, j, k As Long
Dim str, buf As String
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To Cells(i, Columns.Count).End(xlToLeft).Column
For k = 1 To Len(Cells(i, j))
str = Mid(Cells(i, j), k, 1)
'↓のIF文内　前者のスペースは「半角スペース」・後者のスペースは「全角スペース」で入力
If str = " " Or str = "　" Then
str = "" '←続けて""を入力
End If
buf = buf & str
Next k
Cells(i, j) = buf
buf = ""
Next j
Next i
End Sub

以上、お役に立てばよいのですが、
ダメならごめんなさいね。m(__)m

回答No.3

xls88

１）他に、【＠】と【変化】に類似する文字が無いのなら

B列の対象セル範囲を選択
編集メニューの「置換」で
検索する文字列に、【*】
置換後の文字列に、何も入力せず空白のまま
で「すべて置換」

これでスペースが残ることなく特定文字列を削除できます。
それでもスペースが残るようなら
置換後の文字列入力欄でBackSpaceキーを操作し、全く何もない状態を確認してください。

２）類似する文字があるのなら、【＠】と【変化】個々に「置換」を行ってください。

B列の対象セル範囲を選択
編集メニューの「置換」で
検索する文字列に、【＠】
置換後の文字列に、何も入力せず空白のまま
で「すべて置換」

＞二つの文字列に対して別々にやるようなので、記録マクロで一括で処理しています。
「新しいマクロの記録」で得られたコードがあるなら提示してみてください。

お礼

xls88　様　ありがとうございました。
　言葉足らずで申し訳ありません。
　記録マクロは以下の通りです。

Sub Macro1()
Cells.Replace What:="【＠】*", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("L5").Select
Cells.Replace What:="【変化】*", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

このマクロを実行後、「行」の自動調整をするのですが、最終行全部とその前の行（この行には検索をかけた特定文字列の直前の文字列がありますが）に表示上は空白ですがカーソルでなぞると反転表示され選択される部分が残ります。選択したまま削除すると「行」の自動調整が働いて空白部分がつまります。しかし、この削除処理をしないと自動調整がきかず空白のままなのです。この一行ちょっとのことを「スペース」と表現してしまいました。該当セルが多数のため手動での上記の処理は避けたいです。
この「スペース」を削除して自動調節を効かせる方法、あるいはマクロの手直しを教えていただけないでしょうか。

回答No.2

noname#192382

あなたのマクロの中で置換後の文字のところに空白でなく、NULL(なにも入力しない)に指定すればうまくいくと思います。

お礼

optimumsoup　様　ありがとうございました。

　言葉足らずで申し訳ありません。
　記録マクロは以下の通りです。

Sub Macro1()
Cells.Replace What:="【＠】*", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Range("L5").Select
Cells.Replace What:="【変化】*", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

このマクロを実行後、「行」の自動調整をするのですが、最終行全部とその前の行（この行には検索をかけた特定文字列の直前の文字列がありますが）に表示上は空白ですがカーソルでなぞると反転表示され選択される部分が残ります。選択したまま削除すると「行」の自動調整が働いて空白部分がつまります。しかし、この削除処理をしないと自動調整がきかず空白のままなのです。この一行ちょっとのことを「スペース」と表現してしまいました。該当セルが多数のため手動での上記の処理は避けたいです。
この「スペース」を削除して自動調節を効かせる方法、あるいはマクロの手直しを教えていただけないでしょうか。
NULLに指定する方法も教えていたけると助かります。一応ネットで調べたんですが・・・

回答No.1

noname#204879

「置換」でも「適当なマクロ」でもない別解です。
D1: =IF(ISERROR(FIND("【＠】",A1)),1000,FIND("【＠】",A1))
E1: =IF(ISERROR(FIND("【変化】",A1)),1000,FIND("【変化】",A1))
B1: =LEFT(A1,MIN(D1:E1)-1)

お礼

mike_g　様　ありがとうございました。
詳しく教えていただいて助かりました。
とても勉強になりました。
簡単で恐縮ですが、お礼申し上げます。