セルの最終行を調べる場合

Question

G列の最終行を調べる際下記のように記述したところ
変数Lrには1が代入されます。

Lr = Datasheet.Cells(Rows.count, 7).End(xlUp).Row

実際は数万行ありG列には途中に空白はありません。

確認の為、該当のシートのG列の最終行(1048576行）までカーソルを移動し「Ctrl+↑」で確認しましたが、目標のセルまでカーソルが移動致しました。

下記のようにRangeのEndプロパティを使うと正しく変数に代入できました。
Lr = Datasheet.Range("G1").End(xlDown).Row

作成しているプロシージャはMainプロシージャからCallで呼び出してシートのコピー処理をして戻すFunctionとなります。

該当プロシージャは下記のように１列目の最終行の最終行を調べて元データからコピーしておりますが、こちらは正しく最終行の代入ができております。

With Datasheet
Lr = .Cells(Rows.count, 1).End(xlUp).Row
.Cells(Lr, 1).PasteSpecial
End With

Rangeを使えば動くので問題はないのですが、何故、正しく代入できないのか、
違いがわからず今後の為に参考にさせて頂ければ思います。
ご教授よろしくお願い致します。

参考までコードを載せておきます。

Function FileCopy()

Dim MenuSheet As Excel.Worksheet
Dim DataSheet As Excel.Worksheet
Dim Target As String
Dim Lr As Long

Set MenuSheet = Workbooks("XXX.xlsm").Worksheets.Item("YYY")
Set DataSheet = Workbooks("XXX.xlsm").Worksheets.Item("ZZZ")

Target = MenuSheet.Cells(2, 3).Value 

With Datasheet
  　.AutoFilterMode = False
    Lr = .Cells(Rows.count, 7).End(xlUp).Row　'←G列の最終行番号を取得したいのですが正しく代入できません。（1が代入されてしまいます）
    'Lr = .Range("G1").End(xlDown).Row  '←こちらは正しくG列の最終行番号が代入できます。
End With

DataSheet.Range("A1").AutoFilter 7, Target

With DataSheet.Range("A1").CurrentRegion.Offset(1, 0)
    .Resize(.Rows.count - 1).EntireRow.Delete
End With

DataSheet.Range("A1").AutoFilter

With OriginalSheet '←Public変数 参照元シート
    Lr = .Cells(Rows.count, 1).End(xlUp).Row　'←こちらは正しく代入できます。
    .Range(Cells(2, 1), Cells(Lr, 21)).copy
End With

With DataSheet
    Lr = .Cells(Rows.count, 1).End(xlUp).Row　'←こちらは正しく代入できます。
    .Cells(Lr, 1).PasteSpecial
End With

Application.CutCopyMode = False

End Function

【追記】
G列の最終行番号を取得する理由
まだ、コード化していないのですが、変数TargetとG列の最終行をマッチングさせたい。
次のようなコードを考えております。
If Datasheet.Cells(Lr, 7).Value <> Target Then
MsgBox "エラーメッセージ"
exit function
End if

Comments

[jinoji]

「正しく代入できません。」と書かれた行のLr は何のために取得しようとしているのですか。

[shinyakita]

承知致しました。
修正させて頂きます。

[jinoji]

いえ、G列の最終行番号を取得する理由（取得したあとそれを何に使うのか）が分からなかったのです。

[shinyakita]

申し訳ございません。
追記させて頂きました。

[jinoji]

追記の内容を拝見すると、
If Datasheet.Cells(Rows.count, 7).End(xlUp).Value <> Target Then
とするとよさそうだと思いました。
（他の方も書いていらっしゃいますが、元のコードでも意図通り取得できるはず、と思ったので、取得後のどこかで何かを取り違えたのではないかと推測し質問しました。）

[shinyakita]

RangeのEndプロパティを使用すると取得できてCellsのEndプロパティを使用すると取得できない（1になる。)理由が不明で悩んでおります。
変数に代入する事なく、直接IFに書き込むのですね。

[jinoji]

Lr = .Range("G1").End(xlDown).Row と同じ方向からアプローチするなら
Lr = .Cells(1, 7).End(xlDown).Row となると思います。
逆に
Lr = .Range("G" & Rows.count).End(xlUp).Row
という書き方も考えられます。
４通り書いて、Debug.Printしてみたらどうでしょう。

[shinyakita]

RangeのRows.countは考えておりませんでした。
テストしてみます。

[jinoji]

あと強いて言えば Rows.Count を .Rows.Count にするとかですかね。

[shinyakita]

次のように書換てテスト致しました。
With DataSheet
.AutoFilterMode = False
Lr = .Cells(Rows.count, 7).End(xlUp).Row
Debug.Print Lr　　→　結果　1

Lr = .Cells(1048576, 7).End(xlUp).Row
Debug.Print Lr　　→　結果　67287

Lr = .Range("G1").End(xlDown).Row
Debug.Print Lr　　→　結果　67287

Lr = .Range("G" & Rows.count).End(xlUp).Row
Debug.Print Lr　　→　結果　1
End With
rows.countを使うと正しく取得できないようです。

また、ご指摘のあった「If .Cells(Rows.count, 7).End(xlUp).Value <> Target Then」はうまく動きませんでした。

[jinoji]

Debug.Print rows.count
Debug.Print DataSheet.rows.count

[shinyakita]

Debug.Print rows.count　→65536
Debug.Print DataSheet.rows.count　→1048576

Mainプロシージャで開いた元データのエクセルの最終行は65536行なのでどうやら元データを参照しているようです。

[jinoji]

やはりそういうことでしたか。
Lr = .Cells(Rows.count, 7).End(xlUp).Row
を
Lr = .Cells(.Rows.count, 7).End(xlUp).Row
にすれば解決しそうですね。

[shinyakita]

下記のように「.Row.count」とRowの前に「.」を付けたところ正しく取得できました。
With DataSheet
　　　.AutoFilterMode = False
　　　Lr = .Cells(.Rows.count, 7).End(xlUp).Row
End With

Mainプロシージャで開いたエクセルファイルがアクティブになっていた為、Row.countの参照先が元データになっておりました。
こんなことに気が付かず申し訳ございません。

[shinyakita]

解決致しました。
ベストアンサーに選びたいと思いますので、回答の方に投稿お願い致します。

Answer 1

コメント欄でのやり取りの結論を転記します。

With DataSheet
　　　.AutoFilterMode = False
　　　Lr = .Cells(.Rows.Count, 7).End(xlUp).Row
End With

単に Rows.Count と書くと、その時のActiveSheetの最大行数が取得されるため、
それが処理中のシートの最大行数と一致しない場合には意図しない結果になります。
これを、.Rows.Count（With句の中なので DataSheet.Rows.Count の意）とすることで、
意図通りの結果を導くことができます。

Comments

[shinyakita]

お時間を取って頂きありがとうございます。

アクティブシートがどこにあるのかを意識しておくべきでした。
不具合の見つけ方も含め大変勉強になりました。

Answer 2

開示頂いたコードが動くように
XXX.xlsmを作成し
シートYYY,ZZZを作成
適当にセルに値を入れて下記コード（一部抜粋）を実行しました。

Dim MenuSheet As Excel.Worksheet
Dim DataSheet As Excel.Worksheet
Dim Target As String
Dim Lr As Long

Set MenuSheet = Workbooks("XXX.xlsm").Worksheets.Item("YYY")
Set DataSheet = Workbooks("XXX.xlsm").Worksheets.Item("ZZZ")

Target = MenuSheet.Cells(2, 3).Value 

With Datasheet
  　.AutoFilterMode = False
    Lr = .Cells(Rows.count, 7).End(xlUp).Row　'←G列の最終行番号を取得したいのですが正しく代入できません。（1が代入されてしまいます）
    Lr = .Range("G1").End(xlDown).Row  '←こちらは正しくG列の最終行番号が代入できます。
End With

'コピペはここまで

正しく代入できません

としている方も私の環境では最終行番号が取得できました。

可能性としては
0. プログラムの問題ではなく、Excelシートの値の問題
0. まだ開示されていない情報がある

かと思われますが・・・

Comments

[shinyakita]

検証までして頂きありがとうございます。
エクセルシートに問題がある場合、Rangで取得できてCellsで取得できないケースパターンがわからず、可能性でもわかれば教えて頂けると助かります。
また、エクセルシートは何度なく、空白の有無の確認しております。

コードはこれで全てです。
ただし、Mainプロシージャ（コピー元の所定のファイルをオープンしクローズ）から呼び出しておりますが、そちらは公開しておりません。