【マクロ】転記ツール。転記先にデータがある場合、上書きするか消すか質問をして欲しい

下記のマクロをご覧ください。動きます

質問は、添付画像、右下のようにオートフィルターがかかっている状態にて
マクロを実行すると
5行目の何もないところにデータが転記されて欲しいですが、されません

図左下の4行目のデータを、上書きしてよいかの質問もなしにて
図の右下の４行目に転記元のデータに【書き換えてしまいます】

やりたいことは、オートフィルターが入っていても
【5行目に転記をしてほしいです＝最終行】

最悪、できなければ、上書きしてよいか【質問をして欲しいです】
ご存じの方いましたら、教えて下さい

～マクロについて～
・転記先ファイルは共有
・複数人にて利用

【マクロ内容】
・データを転記する
・book1のA1からI1までを、book2のB2からJ2の【最終行】に転記
・転記先の行に既に、データが他メンバーより、入力されていれば、自分のデータか、相手のデータか
⇒どっちをいかすか、質問が出る。エクセル標準機能。
・例：添付画像にて実行しようとした時にエクセル５行目にデータ転記されるが、もし、先に
⇒他メンバーがデータを入力済みであれば、どっちのデータを、いかすか質問が出る

【コード】
Sub 本1()

Dim ws1 As Worksheet
Dim wb2 As Workbook
Dim ws2 As Worksheet

Dim maxrow2 As Long
Dim row2 As Long
Dim i As Integer

Set ws1 = Worksheets("転記元")

For i = 1 To Workbooks.Count

If Workbooks(i).Name = "book2.xlsx" Then '転記先のブック。

Set wb2 = Workbooks(i)

End If

Next

Set ws2 = wb2.Worksheets("転記先") '転記先シート

maxrow2 = ws2.Cells(Rows.Count, "B").End(xlUp).Row '転記先のブックB列

row2 = maxrow2 + 1

ws2.Cells(row2, 2).Resize(1, 8).Value = ws1.Range("A1").Resize(1, 8).Value '転記元から転記先へ7セル分を転記.a1から７セル分。天気先。１列目から。

ws1.Range("I1").Copy ws2.Cells(row2, 10).Resize(1, 1)

wb2.Save '転記先のブックを上書き

End Sub

No.2 ベストアンサー 回答者： fujillin 回答日時： 2023/10/30 10:25

No1です。

＞オートフィルターにて最終行が非表示になっていても
＞以下の方法であれば、途中行ではなく、最終行を
＞見つける事が出来るという事でしょうか？
はい。
試してみれば、すぐにわかることですが・・
非表示行であってもセルの値は取得できますので、1セルずつ調べる方法を取れば、ご質問のように非表示の行があっても最終行を取得できるという意味です。

＞・UsedRange の最下行から上に順に、EMPTYを調査
＞・最終行は、EMPTYの次の行が最終行となる
違います。
下から上に調べる場合は、
　最初に見つかる「Emptyではない行」が最終行。
　その次の行が「最終行の次の行」。
となります。

この回答へのお礼

fujillin
いつもご指導ありがとうございます
・UsedRangeはうまく出来たのですが、最終行が途中に見えないゴミ
　がセルはいっており、最終行がはるか下となり、現実的に
　最終行に転記できませんでした
⇒ゴミは、行削除で消せますが、かなり多くの行にあり
⇒現実的でありまあせんでした

・次にEmptyは、上から数えますが、事例をネットにのっておらずに
　つくれませんでした

・最後に上記をヒントに以下リンクにて、オートフィルターが
　入っていても、正しい最終行の取得・転記できる方法があったので
　同方法にしました
⇒いつも、ご指導ありがとうございます

https://www.excellovers.com/entry/lastrow01

お礼日時：2023/10/30 13:14

No.1 回答者： fujillin 回答日時： 2023/10/30 00:07

こんばんは

ファイル共有時の挙動については環境がないので、試せませんけれど・・

＞やりたいことは、オートフィルターが入っていても
＞【5行目に転記をしてほしいです＝最終行】
の部分に関しての回答です。

ご提示のコードで、最終行を取得しようとしていると思われる
＞　～.End(xlUp).Row
は、手操作の End+↑ と同じ挙動をします。
この場合、フィルター操作に限らず「非表示の行」は無視されます。
ですので、シート上に非表示行が存在する場合には、正確には「表示されている最終行」を取得することになります。
この結果、ご質問のような事象になっているものと推測します。

「表示されている/いないに拘わらず最終行」を取得なさりたいのであれば、セルを順に調べて最初のEmptyではないセルを調べれば、お求めの結果になるかと。
（その列には空白行が途中に存在しない保証があるなら、上から順に調べて、最初のEmptyなセルを得れば、それが「最終行の次の行」になります）

下から順に調べる場合は、Rows.Count行からでは無駄が多いでしょうから、シートの UsedRange の最下行から上に順に調べてゆくようにすれば宜しいでしょう。

この回答へのお礼

・原因はご指摘のとおりです
>シート上に非表示行が存在する場合には、正確には「表示されている最終>行」を取得することになります。
>この結果、ご質問のような事象になっているものと推測します。

オートフィルターにて最終行が非表示になっていても
以下の方法であれば、途中行ではなく、最終行を
見つける事が出来るという事でしょうか？

・UsedRange の最下行から上に順に、EMPTYを調査
・最終行は、EMPTYの次の行が最終行となる

お礼日時：2023/10/30 07:41