[[20230526155322]]　『空白セルを削除』（パラダイス銀河）　ページの最後に飛ぶ

『空白セルを削除』（パラダイス銀河）

こんにちは。

空白セルを削除したいけど削除できません。

【Sheet1】　各セルには該当する場合表示する式が入ってます。
A列は桃のみ、B列はビワのみ、C列は梨のみ...が表示されます。　　

　　　A列　 B　　C　　
1行目桃　　　　なし
2　　　　　ビワ
3 　　桃　ビワ　
4 　　桃　
5　
6 　　桃　ビワ　なし
7
8　
9　　　　　ビワ　　

Sheet1をSheet2に行列入れ替えでコピペすると以下のようになります
Worksheets("Sheet1").Range(Cells(i, "A"), Cells(i, "C")).Copy

Worksheets("Sheet2").Cells(LastRow, "A").PasteSpecial xlPasteValues, SkipBlanks:=True, Transpose:=True

※SkipBlanks:=Trueで空白セル無視で貼り付けてるつもりだけど空白セルごと貼り付けられてる

【Sheet2】

　　　A列
1行目　桃　
2
3　　　なし
4
5　　　ビワ
6　
7　　　桃
・
・
・

以下のコードでも2，4，6...行目の空白は削除できない
Worksheets("Sheet2").Range("A1:A100").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

★やりたいこと
最終的には【Sheet2】に行列入れ替えして【A列】に【空白行なし】で表示したい

【Sheet2】

　　　A列
1行目　桃　
2　　　なし
3　　　ビワ
4　　　桃　
5　　　ビワ
6　　　桃
7　　　桃
8　　　ビワ
9　　　なし
・
・

★できないこと
空白削除
空白のように見えて空白ではないので、削除できないかと思います。
みなさんだったらどうしますか？？

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

その時の状況で空白セルかどうか確認する。
（大事） 2023/05/26(金) 17:15:12

■１
たぶん、【空白をセル無視する】のことを勘違いしているとおもいます。
イメージとしては、貼り付けるときに【データを上書きしない】という感じであって、コピーするときに対象にしないではないです。
なので、空白に【空白を貼り付けしない】でも空白のままです。
■２
＞以下のコードでも2，4，6...行目の空白は削除できない
空白と表現されているセルは本当に空白ですか？

＞空白のように見えて空白ではないので、削除できないかと思います。
ご自身でも気づかれているように、例えば数式で""になっていれば、それは文字数0の文字列が設定されています。

■３
＞みなさんだったらどうしますか？？
A列最終行から順にみていき、セルの値が""なら該当セルを削除して上に詰めます。

■４
なお、コピー元の"空白"セルが純然たる空白であれば、SpecialCellsを使ってコピーするセルを限定するという手もありますが、"定数"と"数式"がまぜこぜだったりしたら、手当てすることが増えるので個人的には非推奨です。

 ※私が知らないだけで"定数"と"数式"を同時に指定する方法があったらごめんなさい。

（もこな２） 2023/05/26(金) 17:22:39

 かぶったけど

 空白セルを無視する設定は、空白を詰めて貼り付けるのではなく、コピー先に入力があれば、
 空白で上書きしない仕様です。
    |[A]|[B]
 [1]|A  |   
 [2]|B  |   
 [3]|   |X  
 [4]|C  |   
 [5]|D  |   
 の場合、A列をB列に貼り付けると、B3のXが空白になりますが、空白を無視するとした場合、
 B3のXを残したまま貼り付けができる、という意味です。
    |[A]|[B]
 [1]|A  |A  
 [2]|B  |B  
 [3]|   |X  
 [4]|C  |C  
 [5]|D  |D

 2については、「空白に見える」セルの値を取得して、置換（Replaceメソッド）してからDeleteさせます。
（稲葉） 2023/05/26(金) 17:30:22

 試したけど、Copyだと""はSpecialCellsで空白と認識してくれないけど
 Valueでの代入なら空白として認識してくれてるっぽい
 ""以外の改行コードとかだと別だから、↓試してもらって、ダメだったらReplace設定してもらえばいいと思う。
    Sub test()
        Dim i As Long
        Dim r As Range
        i = 1
        Do
            Set r = Sheets("Sheet1").Range("A:C").Rows(i)
            If WorksheetFunction.CountA(r) = 0 Then Exit Do
            Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(3).Value = Application.Transpose(r.Value)
            i = i + 1
        Loop
        With Sheets("Sheet2")
            .Cells.Replace "CODEで調べた空白に見える文字", ""
            .Range("A1", .Cells(Rows.Count, "A")).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        End With
    End Sub
（稲葉） 2023/05/26(金) 17:54:30

追加で。

■５
オートフィルタならば、【純粋な空白】と【""】を区別しない（どちらも【""】として扱う）ので、

 (1) 貼り付け結果にオートフィルタを設定して""を抽出する

 (2) オートフィルタが設定されている範囲が含まれる行を削除する
　　（オートフィルタの仕様で抽出中にオートフィルタ範囲全体を指定して削除しても非表示行は削除されない）

というアプローチでもＯＫだとおもいます。
コードにするとこんな感じ（完成品のプレゼントではなく研究資料として提供します）

    Sub 研究用()
        Dim i As Long
        Dim dstRNG As Range

        Stop

        Set dstRNG = Worksheets(2).Range("A2") '項目行の分はあけておく
        With Worksheets(1).Range("A1:C9")
            For i = 1 To .Rows.Count
                .Rows(i).Copy
                dstRNG.PasteSpecial Paste:=xlPasteAll, Transpose:=True
                Set dstRNG = dstRNG.Offset(.Columns.Count)
            Next i
        End With

        With Worksheets(2)
            .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).AutoFilter Field:=1, Criteria1:=""
            .AutoFilter.Range.EntireRow.Delete
        End With
    End Sub

（もこな２） 2023/05/26(金) 20:11:24

みなさま、ありがとうございます！
なんか思考停止して手が止まってしまったので質問させていただきましたました。

空白無視は完全に勘違いしてました。分かりやすく説明いただきありがとうございます。勉強になりました。

オートフィルタ、Valueでの代入、Replaceなど、いろいろなアイデア！
自分ではとうてい思いつかなかったので、おもしろいです。勉強させていただきます。

ここに書き込む前に色々調べたつもりなんです。
式の入った行や列をコピペして空白は削除、なんてよくあることで、すぐ調べられるかな～と思ったけど、まったくヒットせずでした。
調べるのにも検索方法や調べる場所など、色々あるんでしょうね。

教えていただいたやり方をやってみて、また分からなかったら質問させていただきます。

（パラダイス銀河） 2023/05/26(金) 21:57:33

 >調べるのにも検索方法や調べる場所など、色々あるんでしょうね。

知識と経験かなぁ。。。。

やりたいことは、エクセルの機能で簡単には出来ない（いくつかの手順を踏む必要がある）ので、
マクロの出番かなというところです。（数式でもできるかもしれないけども）

今回の件の場合、シンプルに一つ一つ見て行けばいいのでは？

Option Explicit

Sub test001()

    Dim rngFrom As Range: Set rngFrom = Worksheets("Sheet1").Range("A1").CurrentRegion
    Dim rngTo As Range: Set rngTo = Worksheets("Sheet2").Columns(1)
    Dim c As Range
    Dim i As Long

    For Each c In rngFrom.Cells
        If IsEmpty(c.Value) = False Then
            i = i + 1
            rngTo.Cells(i).Value = c.Value
        End If
    Next
End Sub

Sub test002()

    Dim rngFrom As Range: Set rngFrom = Worksheets("Sheet1").Range("A1").CurrentRegion
    Dim rngTo As Range: Set rngTo = Worksheets("Sheet2").Range("A2")
    Dim vv As Variant: vv = WorksheetFunction.Transpose(rngFrom.Value)
    Dim v As Variant
    Dim Results() As Variant

    Dim i As Long
    ReDim Results(UBound(vv))
    For Each v In vv
        If IsEmpty(v) = False Then
            i = i + 1
            Results(i) = v
        End If
    Next

    rngTo.Resize(i).Value = WorksheetFunction.Transpose(v)
End Sub

（まっつわん） 2023/05/29(月) 13:52:56

まっつわんさま、ありがとうございます。
色々なやり方があるのですね。
こちらのコードも研究させていただきます。

いま、順番に今稲葉さんからｺｰﾄﾞを研究中です。
稲葉さん記載のReplaceメソッド、なぜ機能するのか謎です。

 With Sheets("Sheet2")
    .Cells.Replace "CODEで調べた空白に見える文字", ""
    .Range("A1", .Cells(Rows.Count, "A")).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 End With

Replaceメソッドで"CODEで調べた空白に見える文字"を空白に置き換えて、空白セルを一括削除していると認識してます。

この"CODEで調べた空白に見える文字"という指定で、なぜ空白に置換できるのかという。
"CODEで調べた空白に見える文字"は"あいうえお"でも"1"でも置換できます。

Replaceメソッドの"CODEで調べた空白に見える文字"は検索するデータを指定していると思います。
セルに「CODEで調べた空白に見える文字」と入力されていれば空白に置換できるのは分かるんですけど。

なぜ置換できるのか謎です。

（パラダイス銀河） 2023/05/30(火) 11:48:45

 いや・・・・そこは自分で直してくれって意味です。
 こちらはそのセルに何が入力されているのかわからないので
 先に述べた改行コードなのか、何なのか自分で調べて入れてほしいという意味でした。

https://www.shegolab.jp/entry/excel-ghost-cells
（稲葉） 2023/05/30(火) 12:24:12

稲葉さま

URLありがとうございます。
自分の説明が悪くて質問の意図を伝えられてないようなので、もうちょい質問文を考えてみます。

Sheet1にはExcel関数のIF関数(該当しない場合は""表示するIF関数)がもともと入ってます。

稲葉さんのSub test()実行結果

【Sheet2】
　　　A列
1行目　桃　
2　　　なし
3　　　ビワ
4　　　桃　

このコードもそのまま実行しています。空白削除できています。
1 With Sheets("Sheet2")
2 .Cells.Replace "CODEで調べた空白に見える文字", ""
3 .Range("A1", .Cells(Rows.Count, "A")).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
4 End With

仮にこの部分を以下に変えても実行結果は同じです。空白削除できています。
2 .Cells.Replace "マツケンサンバ", ""

なんで"マツケンサンバ"で空白に置換できんねん！？という謎なんです。

Replace "マツケンサンバ", ""　は、"マツケンサンバ"を検索して空白に置き換える、というメソッドという認識です。

Sheet1にもSheet2にもマツケンサンバなんて入力してないのにー！という思いです。

他の人から見ると、「いやいや置換できるのは当然やん」という感じなんでしょうか。
頭悪すぎる質問でしたらすいません。

（パラダイス銀河） 2023/05/30(火) 14:46:33

＞なんで"マツケンサンバ"で空白に置換できんねん！？という謎なんです。
多分、勘違いしてるんじゃないですかね。結論として【置換してません】

前段として、Valueに""を入れるとempty扱いになるよって話なだけですよね。
例えば↓だと、値貼り付けしたB3セルは""が入った状態になりますが、Valueプロパティを参照したC3セルは純粋なブランクセルになるということかとおもいます。

    Sub 研究用()
        With Worksheets.Add(after:=Worksheets(Worksheets.Count))
            .Range("A1").Formula = "い"
            .Range("A2").Formula = "ろ"
            .Range("A3").Formula = "="""""
            .Range("A4").Formula = "に"
            .Range("A5").Formula = "ほ"

            .Range("A1:A5").Copy
            .Range("B1").PasteSpecial Paste:=xlPasteValues 'B列
            .Range("C1:C5").Value = Range("A1:A5").Value 'C列

            .Range("A1:C5").SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed
        End With
    End Sub

すなわち、↓がミソでしょう(Replaceメソッドが仕事したんじゃなくて、前段でブランクセルに変わっていただけ)

 Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(3).Value = Application.Transpose(r.Value)
                                                                      ~~~~~~                           ~~~~~

（もこな２） 2023/05/30(火) 18:45:36

もこな２さま

ありがとうございます。おっしゃる通りでした。
というか稲葉さんも「ダメだったらReplace設定してもらえばいいと思う。」と記載してくれてますよね。

ぼけた質問でした。

もこな2さん、稲葉さん、改めてありがとうございました。

（パラダイス銀河） 2023/06/02(金) 16:05:25

コメント返信：

[ 一覧(最新更新順) ]

[[20230526155322]] 『空白セルを削除』（パラダイス銀河） ページの最後に飛ぶ

[[20230526155322]]　『空白セルを削除』（パラダイス銀河）　ページの最後に飛ぶ