エクセル マクロ(VBA) 空白&重複の削除

質問

データの空白と重複を削除(上詰め)し、10行目から並べる
というマクロを書きたいです。

下記のマクロを実行すると、添付の緑色のセルのように、
なぜか空白の部分ができてしまいます。
緑色の空白を詰めるには、どのように書けばよいでしょうか。

さらに、Sheet2のデータを元にマクロを実行した時に、そのデータは
変更せずに、結果をSheet3に表示したいのですが、その方法を教えて下さい。
(現在のマクロだと、実行元のデータがある同じシート内で整列されます。)
-
Sub macro1()
Dim i As Long
On Error Resume Next
For i = 1 To 10
With Columns(i)
.SpecialCells(xlCellTypeBlanks).Delete shift:=xlShiftUp
.RemoveDuplicates Columns:=1, Header:=xlNo
End With
Next i
Range("1:9").Insert shift:=xlShiftDown
End Sub
-

回答No.2

keithin · ベストアンサー

＞マクロの結果をSheet3に反映させていのですが、

はて？
シート２じゃなくシート３にするだけですが。

sub macro2()
　dim i as long
　worksheets("Sheet3").range("A:J").value = worksheets("Sheet1").range("A:J").value
　worksheets("Sheet3").select

　on error resume next
　for i = 1 to 10
　　with columns(i)
　　　.textocolumns destination:=cells(1, i), datatype:=xldelimited, fieldinfo:=array(1, 1)
　　　.specialcells(xlcelltypeblanks).delete shift:=xlshiftup
　　　.removeduplicates columns:=1, header:=xlno
　　end with
　next i
　range("1:9").insert shift:=xlshiftdown
end sub

＃丸投げで全部やって貰わないとご自分じゃ全く何もできない/やりたくないなら、最初から丁寧に「ホントにヤリタイ事を」ご相談に書いてください。と、一言ぐらい言わせてもらってもバチは当たらないと思います。
＃そんなつもりじゃありませんというのでしたら、ついでにちょこちょこっと聞いちゃえと「ついで感覚」で補足投稿をしているから説明が足りません。ご自分の説明不足で伝えきれていなくて、意図と違う回答が寄せられてしまった状況をキチンと踏まえて、ホントにやりたかった事を詳しく「新しいご相談として」丁寧に作成し直して、改めて投稿するようになさってください。

しかし、そもそもで言うと

＞Set rngListとrngResultを設定しないといけないのでしょうか。

そもそもこれは何か他のご相談の、他の方から寄せられたマクロについての追加ご質問ではありませんか？
私からはそんな内容は全く触れていませんので、こちらにお問い合わせいただいても困ります。
ほっぽっとくのも一つの対応なんですが、こちらの件についてもまぁ一言言わせてもらう事にしました。

回答No.1

keithin

なるほど。シート１が、「実際には数式が入っていた」のがナイショだったのが原因ですね。

sub macro2()
　dim i as long
　worksheets("Sheet2").range("A:J").value = worksheets("Sheet1").range("A:J").value
　worksheets("Sheet2").select

　on error resume next
　for i = 1 to 10
　　with columns(i)
　　　.textocolumns destination:=cells(1, i), datatype:=xldelimited, fieldinfo:=array(1, 1)
　　　.specialcells(xlcelltypeblanks).delete shift:=xlshiftup
　　　.removeduplicates columns:=1, header:=xlno
　　end with
　next i
　range("1:9").insert shift:=xlshiftdown
end sub

＃補足
上述は数式が計算している""を除去します。

それとは別に、たとえば「スペース」とかが紛れてると、このやり方では取り除けない場合もあります。
カンペキを期すならシート１にオートフィルタを取り付けて、「列ごとに」（空白）で絞って見た目空っぽのセル範囲を選んでＤｅｌｅｔｅキーを押す、Ａ列をやったら次はＢ列…とＪ列まで操作を行った方が確実です。

また今の「シート１（じゃないですけど）」の数式で””を計算させるのを止めて、もっと処置しやすい例えば「エラー値（NA()など）」でも計算させておいて、マクロでそれを（空白セルを除去させた代わりに）除去したほうが判りやすいです。

補足

ありがとうございます!

お陰様で、ほぼ思うような動きになりました。

マクロの結果をSheet3に反映させていのですが、
Set rngListとrngResultを設定しないといけないのでしょうか。
教えていただけませんでしょうか。