vbaエクセルマクロについて あるデータを作成し、デスクトップに.xlsx形式で保存するマクロを作成

vbaエクセルマクロについて

あるデータを作成し、デスクトップに.xlsx形式で保存するマクロを作成しました。

その.xlsx形式で保存したものを別の仕組みに投入すると処理が実行されます。
内容に不備があると｢取り込みできません｣というエラーになります。

私が作成したマクロでは、
マクロ1→別のデータを加工し新データを作成
マクロ2→全体をコピーして値で貼り付け
マクロ3→RemoveDuplicatesで重複行があれば削除
マクロ4→デスクトップに.xlsx形式で保存
この4つの起動をかけるようになっています。

このマクロでできた.xlsxを仕組みに投入するとエラーが出ることなく、うまくいきます。

ただ、この.xlsx、1度でも上書き保存すると投入時エラーになりうまく処理がされない、という現象が起こります。
（内容を全く変更せず、上書き保存をするだけでエラーになります）

そこでいろいろと試したところ、
マクロ1→マクロ2→マクロ4のみで作成すると、上書き保存してもエラーが出ないことがわかりました。
内容を手で直接入力して変更し、上書きしても問題なく処理されます。

しかし、
マクロ3、RemoveDuplicatesのマクロを含む、
1、2、3、4を全て起動して出来上がったものは、そのままだと投入できて処理がはしるのですが、1度でも上書きすると投入できずエラーになってしまいます。

さらにいろいろと試して
下のほうの空白行を削除して上書き保存すると、エラーにならずうまくいくことがわかりました。

空白行に何かが影響を与えるのでしょうか。
考えられることを何でもいいので教えていただけますとたすかります。
よろしくお願いいたします。

質問者からの補足コメント

• kantansi様、ありがとうございます！
  たとえば、マクロ4を起動（デスクトップに保存）する前に、下のほうの空白行を全て削除する、というプログラムを入れることによって回避することもできますでしょうか？

    補足日時：2023/03/03 20:02

• 補足
  
  重複したものがない場合でもエラーが出ます
  たとえば100行のデータがあり、重複したものがない場合、RemoveDuplicatesメソッドを起動しても100行のデータとなり、空白行が間にはさまることはありません。
  101行目から下の行を全て削除するとうまくいくようになります。
  
  重複行があり、100行が80行になったとしても、空白行が間にはさまることはありません。
  81行目から下の行を全て削除するとうまくいきます。

    補足日時：2023/03/03 20:39

• 1048576行目を削除ではうまく取り込めませんでした。
  目で見えているデータセルの最終行の1行下（空白行に見えている）行を削除すると取り込みできました。

    補足日時：2023/03/06 15:14

No.2 ベストアンサー 回答者： Qchan1962 回答日時： 2023/03/04 01:16

こんばんは

値貼り付けしたデータで
＞データ群下の行を全て削除するとうまくいく・・・素朴な疑問
データ群内でCtrl+shift+＊を押すとデータ群のみが選択され　ない　のでは？

値貼り付けで関数などで出力した""がペーストされていませんか？
もし選択範囲が値（実数）のある範囲以外も選択されているのであれば
マクロ１やマクロ２を見直す方が簡単かもしれません

見直す・・データ群に空白行が挟まれない事が確定的ならば
最大行のある列で（A列なら）Range("A1").End(xlDown).Rowで最大行№
列は最大列のある行で.End(xlToRight).Columnとか

例えば
Sub Macro2()
Dim c As Long, r As Long
Dim ary As Variant
With ActiveSheet
'A列1行目共に最大の行数列数がある基点（）途中に空白セルが挟まない）
c = .Range("A1").End(xlToRight).Column
r = .Range("A1").End(xlDown).Row
ary = .Range("A1", .Cells(r, c))
End With
Sheets(2).Cells.Clear
Sheets(2).Range("A1").Resize(UBound(ary, 1), UBound(ary, 2)) = ary
End Sub

ActiveSheetの値のある範囲（条件が限定的ですが）を配列に入れて
Sheets(2)　インデックス２のシートへ出力しています

Sheets(2)でCtrl+shift+＊を押すとデータのある範囲のみ選択されるハズ
これならRemoveDuplicatesメソッド後に削除なんて多分しなくて良いかと

想像（想定）が含まれた回答です

No.6 回答者： Qchan1962 回答日時： 2023/03/04 19:33

＃５コピペずれました

ブックオブジェクト.シートオブジェクト.レンジオブジェクト.Value _
= ブックオブジェクト.シートオブジェクト.レンジオブジェクト.Value

右辺と左辺は同サイズで必ずValueプロパティを付けます

この回答へのお礼

たくさん回答いただき、本当にありがとうございます。
まだ試すことができていませんが、
教えていただいたことを無駄にせず、今後に役立てていきたいと思います！

２回目に回答いただいた内容をまずは試したいと思います。
目で見えているデータの下の""行まで重複行削除が対象になっている場合、
目で見えているデータの1行下に、代表して残った""の行があるから、その行さえ削除すればうまくいくのではないか、ということなのかな、と解釈しました(^^;
違っていたらすみません。

親身になり回答くださったこと感謝します！
本当にありがとうございます。

お礼日時：2023/03/05 01:19

No.5 回答者： Qchan1962 回答日時： 2023/03/04 19:29

マクロ2の値貼り付けをやめる

セルが入力モードになるとEmptyになるようなので
　（こんなんだったかな？）

ブックオブジェクト.シートオブジェクト.レンジオブジェクト.Value _
ブックオブジェクト.シートオブジェクト.レンジオブジェクト.=.Value
に変え　代入式　にする

コード例
Sub sample()
Dim rng As Range
Set rng = Sheets(1).Range("A1").CurrentRegion
Sheets(2).Range("A1").Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End Sub

シート１の値範囲（A1セルを基軸に空白セルに囲まれた範囲）を
シート2のA1セル以下に代入しています

この方法ですと＃４の最終行取得ロジックはすべて同じセル№を示すと思うのですが・・・（通常はtest_02でしょうか）

No.4 回答者： Qchan1962 回答日時： 2023/03/04 19:18

＞データがある行の最終行を対象に、重複行を削除する、というプログラムも組めるのでしょうか。

。
こちらは以前掲示した参考サイトにあります
https://learn.microsoft.com/ja-jp/office/vba/api …

ただし、最終行の取得には想像の範疇が含まれます
データ群内でCtrl+shift+＊を押すと表示データ群のみが
選択され　ない　のでは？　どうなのでしょう

繰り返しになりますが、マクロ１で参照式・関数などを使って""をセルの値として代入している　極端な例　=""
この様なセル（行）を含めコピー　値貼り付けを行うと　数式は消えますが""は残ると思います

最終行を取得するサンプル7種です
（一部のロジックはA1セルからデータ群としてある事が条件になります）

Dim i As Long, lastrow As Long
Sub test_01()
lastrow = Range("A1").End(xlDown).Row
MsgBox lastrow
End Sub
Sub test_02()
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox lastrow
End Sub
Sub test_03()
lastrow = Range("A1").SpecialCells(xlLastCell).Row
MsgBox lastrow
End Sub
Sub test_04()
lastrow = Range("A1").CurrentRegion.Rows.Count
MsgBox lastrow
End Sub
Sub test_05()
lastrow = ActiveSheet.UsedRange.Rows.Count
MsgBox lastrow
End Sub
Sub test_06()
For i = 1 To Rows.Count
If Not Cells(i, 1) <> "" Then lastrow = i - 1: Exit For
Next
MsgBox lastrow
End Sub
Sub test_07()
For i = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(i, 1) <> "" Then lastrow = i: Exit For
Next
MsgBox lastrow
End Sub

1つずつ試して実際の取得したい行№になるものを選べばよいと思います
_06と_07でしょうか・・・

03,04,05はA列とは限りません（他はA列が対象列です）

例
ActiveSheet.Range("A1:Z" & lastrow) _
.RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes


続く・・・

この回答へのお礼

回答ありがとうございます！
シート全体の幽霊セル一括除去マクロを使うという手もありますでしょうか。

https://www.shegolab.jp/entry/excel-ghost-cells# …

お礼日時：2023/03/05 01:51

No.3 回答者： Qchan1962 回答日時： 2023/03/04 01:55

＃２です　少し考えました

もし、""が原因ならば、RemoveDuplicatesメソッドで見えない""は1行になっていると思うので最終行１行のみを削除すれば良いのでは無いかと・・・

＞下の行を　全て　削除するとうまく

違うかな？

最終行のみを削除で良い場合
取り敢えずマクロ3で1行をクリアーする方法（削除はなんとなく避けて）
With Worksheets(1)
.Range("A:Z").RemoveDuplicates Columns:=Array(1), Header:=xlYes
.Cells(Rows.Count, 1).End(xlUp).EntireRow.Clear
End With

もし全てなら＃２の手法をマクロ3で

Sub Macro3()
Dim c As Long, r As Long
Dim ary As Variant
With Worksheets(1)
.Range("A:Z").RemoveDuplicates Columns:=Array(1, 3), Header:=xlYes
c = .Range("A1").End(xlToRight).Column
r = .Range("A1").End(xlDown).Row
ary = .Range("A1", .Cells(r, c))　'データを配列で一旦保持
.Cells.Clear　'シートセルを全クリアー
.Range("A1").Resize(UBound(ary, 1), UBound(ary, 2)) = ary
End With
End Sub

連投、すみません

この回答へのお礼

回答ありがとうございます_(._.)_
私のような者には理解が難しいですが、なんとなく少しだけわかったような気がしておりす。

値の貼り付けはデータ部分以外も全てをやっております。

重複行削除は下の通りとしています。

WorkSheet（1）.Range("A:Z").RemoveDuplicates Columns:=Array（1, 3）,Header:=xlYes

さらに質問して申し訳ないですが、
こちらですと、AからZの列を最終行までを対象として重複行削除するようになっているかと思いますが、
データがある行の最終行を対象に、重複行を削除する、というプログラムも組めるのでしょうか。。
もしできるようでしたら、教えていただけますと嬉しいです。_(._.)_

お礼日時：2023/03/04 16:21

No.1 回答者： kantansi 回答日時： 2023/03/03 18:57

おそらく、エラーが発生する原因は、RemoveDuplicatesのマクロで、重複を削除する際に、削除された行のデータが空白行となり、その空白行が後続の処理に影響を与えている可能性があります。

たとえば、RemoveDuplicatesのマクロで、A列とB列の重複を削除すると、B列のデータが空白になり、その空白が問題を引き起こす可能性があります。後続のマクロが、データがあると想定している行に空白行が入ってしまった場合、エラーが発生することがあります。

空白行に何かが影響を与えるのかどうかは、使用しているシステムや投入先の仕組みによって異なります。そのため、空白行を削除することで解決できた場合でも、システムによっては影響を与えることがあります。

解決策としては、RemoveDuplicatesのマクロで空白行を作らないようにすることが考えられます。たとえば、削除する前に空白行を削除することができます。また、RemoveDuplicatesのマクロを使用せず、VBAで重複を削除する方法を採用することもできます。ただし、投入先の仕組みや処理内容によっては、その方法が適切でない場合があります。