質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

2回答

1413閲覧

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

shinyakita

総合スコア39

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2021/07/19 05:39

編集2021/07/19 08:32

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となります。

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

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

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

jinoji

2021/07/19 07:26

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

2021/07/19 07:58

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

2021/07/19 08:20

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

2021/07/19 08:33

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

2021/07/19 22:59

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

2021/07/19 23:15

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

2021/07/19 23:23

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

2021/07/19 23:25

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

2021/07/19 23:33

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

2021/07/20 00:49

次のように書換てテスト致しました。 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

2021/07/20 00:52

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

2021/07/20 01:42

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

2021/07/20 01:46

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

2021/07/20 01:49

下記のように「.Row.count」とRowの前に「.」を付けたところ正しく取得できました。 With DataSheet    .AutoFilterMode = False    Lr = .Cells(.Rows.count, 7).End(xlUp).Row End With Mainプロシージャで開いたエクセルファイルがアクティブになっていた為、Row.countの参照先が元データになっておりました。 こんなことに気が付かず申し訳ございません。
shinyakita

2021/07/20 01:51

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

回答2

0

ベストアンサー

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

VBA

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

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

投稿2021/07/20 01:59

jinoji

総合スコア4585

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shinyakita

2021/07/20 02:11

お時間を取って頂きありがとうございます。 アクティブシートがどこにあるのかを意識しておくべきでした。 不具合の見つけ方も含め大変勉強になりました。
guest

0

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

VBA

1 2Dim MenuSheet As Excel.Worksheet 3Dim DataSheet As Excel.Worksheet 4Dim Target As String 5Dim Lr As Long 6 7Set MenuSheet = Workbooks("XXX.xlsm").Worksheets.Item("YYY") 8Set DataSheet = Workbooks("XXX.xlsm").Worksheets.Item("ZZZ") 9 10Target = MenuSheet.Cells(2, 3).Value 11 12With Datasheet 13  .AutoFilterMode = False 14 Lr = .Cells(Rows.count, 7).End(xlUp).Row '←G列の最終行番号を取得したいのですが正しく代入できません。(1が代入されてしまいます) 15 Lr = .Range("G1").End(xlDown).Row '←こちらは正しくG列の最終行番号が代入できます。 16End With 17 18'コピペはここまで

正しく代入できません

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

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

かと思われますが・・・

投稿2021/07/19 15:17

odataiki

総合スコア938

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

shinyakita

2021/07/19 23:23

検証までして頂きありがとうございます。 エクセルシートに問題がある場合、Rangで取得できてCellsで取得できないケースパターンがわからず、可能性でもわかれば教えて頂けると助かります。 また、エクセルシートは何度なく、空白の有無の確認しております。 コードはこれで全てです。 ただし、Mainプロシージャ(コピー元の所定のファイルをオープンしクローズ)から呼び出しておりますが、そちらは公開しておりません。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問