【iOS版アプリ】不具合のお知らせ

マクロ初心者です。
指定したフォルダ内のExcelファイル(xlsx.)から、値を取得し1つの「集約」シートに貼り付けたいです。
ネットで調べて出てきたコードを組み合わせて以下のマクロを作成してみたのですが、うまくいかない点があり、ご教示いただきたく質問いたします。
現状↓

Sub フォルダ指定()

If Application.FileDialog(msoFileDialogFolderPicker).Show = True Then
Range("b2").Value = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
End If

End Sub

Sub フォルダ内のデータ集約()
read_folder = Range("B2")
read_file = Dir(read_folder & "\*.xls*")
Do While read_file <> ""
output_end_row = Sheets("集約").Range("A65536").End(xlUp).Row
Workbooks.Open read_folder & "\" & read_file
input_end_row = Range("A65536").End(xlUp).Row
Range("C10:R24").Copy
ThisWorkbook.Sheets("集約").Activate
Range("A" & output_end_row + 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Workbooks(read_file).Close
read_file = Dir()
Loop
End Sub

Sub 集約シートクリア()
Worksheets("集約").Cells.Clear
End Sub

直したい部分は以下2点です。
・コピー範囲
 「C10からR24」ではなく、「C10以下最終行までの中で値が入っているセル」に変更したい
・貼り付け先セル
 集約シートのB2セル以下にデータを貼り付けたい

以上2点を反映するにはどのように修正すればいいのでしょうか。
自分の力ではもうお手上げです。
よろしくお願いいたします。

情報不足の部分があればお申し付けください。

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

  • さっそくご回答いただきありがとうございます。
    完全に私の知識不足で申し訳ないのですが、教えていただいたコードを元のどの部分に差し替えればよいか教えていただくことはできますか?

    また、集約シートB2から貼り付けたいという部分ですが、1行目に項目名、A列にNo.を入れてある状態であっても(Rows.Count, "B")を使うことはできますか?

    開いたブックは目当てのシートが表示されるのかという点については、sheet1以外作成しないようなルールにしようと思っていますが問題ないでしょうか。

    No.1の回答に寄せられた補足コメントです。 補足日時:2021/09/24 18:09
  • 何度も申し訳ありません。
    コピーをしたいのは、「C10〜R10以下最終行までの中で値が入っているセル」でした。

    No.2の回答に寄せられた補足コメントです。 補足日時:2021/09/27 09:11
gooドクター

A 回答 (3件)

こんにちは


>C10〜R10以下最終行まで
>Range("C10", Cells(Rows.Count, "C").End(xlUp)).Copy _
のカッコ内が変わると思うのですが、どこに何を入れたらいいのでしょうか。

その通りです
Range("C10", Cells(Rows.Count, "C").End(xlUp))の意味を・・
LastRow=Cells(Rows.Count, "C").End(xlUp).Row
LastRowが仮に20だったとするとRange("C10:C20")と同じですね。

ここは、実験するのが良いと思います。
新規シートで下記を用いて確認してください

Sub test()
Range("A1").Select
Range("C20").Value = "終端"
'範囲指定テスト
Range("C10", Cells(Rows.Count, "C").End(xlUp)).Select
End Sub

Sub test1()
Range("A1").Select
'範囲指定テスト
Range("C10:C20").Select
End Sub

Sub test2()
Dim lastRow As Long
Range("A1").Select
Range("C20").Value = "終端"
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
'範囲指定テスト
Range("C10:C" & lastRow).Select
End Sub

Sub test3()
Range("A1").Select
Range("C20").Value = "終端"
'範囲指定テスト
Range("C10", "C20").Select
End Sub

testとtest3は ,で区切られた形になっている事が分かると思います。
test2は自動記録などで記録される範囲指定と同じで、変数を使い変形したものがtest2です

Range("C10", "C20").Select はC10からC20を選択していますが
このC20をR20に変えればC10:R20となりますね。
testのコード(回答に使われている)を見ると C20に該当する所は
Cells(Rows.Count, "C").End(xlUp)
従って "C"を"R"に変えれば良いように思います。

ただし、(重要)
値のある最終行(コピーしたい行)がC列R列共に「必ず」同じならば良いのですが、R列行が違う場合、正しく目的範囲を取得できませんね。

ここまでお判りになりますでしょうか?これも実験をすると判ると思いますのでやってみてください。

では、どうするか、、方法は色々あります。C列の最終行が基準なら
Resizeを使う方法やtest2を変更する方法などがあります。

Range("C10", Cells(Rows.Count, "C").End(xlUp)).Resize(, 16).Select

可能性は低いと思いますが
最終行が変わる場合は最終行ナンバーを取得する必要があります
これも方法は色々あります。
べたにループを使う場合 (途中の列の20行以上に値を入れ(消し)て確認)
Sub test4()
Dim lastRow As Long, i As Long
Range("A1").Select
Range("C20").Value = "終端"
For i = 3 To 18
If lastRow < Cells(Rows.Count, i).End(xlUp).Row Then
lastRow = Cells(Rows.Count, i).End(xlUp).Row
End If
Next i
'範囲指定テスト
Range("C10:R" & lastRow).Select
End Sub

大体出そろったかな、、
.End(xlUp)についてはご質問コードにもあるので割愛します。

あたりを付けられるスキルがあるので自身で実験(検証)してみましょう
    • good
    • 0
この回答へのお礼

解決しました

ご丁寧にご回答いただきありがとうございました。
範囲指定のテストをしているうちに時間が経ってしまいお礼が遅くなり申し訳ありません。
今回の件、大変勉強になりましたし、自分の勉強不足も実感しました。
最後の最終行取得のパターンがまだ理解できていないので、このあとじっくり勉強しようと思います。
ありがとうございました。

お礼日時:2021/09/28 16:48

>1行目に項目名、A列にNo.を入れてある状態であっても(Rows.Count, "B")を使うことはできますか?



はい、このコードはB列を指定していますので他の列は不問です

>sheet1以外作成しないようなルールにしようと思っていますが問題ないでしょうか。
はい、シートが1枚しか存在しないレギュレーションならば問題ないです。
同様なケース(データファイル)で例えるとCSVファイルが有ります。

>教えていただいたコードを元のどの部分に差し替えればよいか教えていただくことはできますか?

コードの意味を理解する事をやめない事と躓いた原因をしっかり追究すると言う事を約束してください。(私にではなく自身に)

と言う事で判り難いと思いますが、書き直してみます。
分からないコードはコピペで検索、学習してみましょう。

Sub フォルダ内のデータ集約_Test()
Dim read_folder As String
Dim read_file As String

If Application.FileDialog(msoFileDialogFolderPicker).Show = True Then
read_folder = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
End If

read_file = Dir(read_folder & "\*.xls*")
Application.ScreenUpdating = False
Do While read_file <> ""
Workbooks.Open read_folder & "\" & read_file
Range("C10", Cells(Rows.Count, "C").End(xlUp)).Copy _
ThisWorkbook.Sheets("集約").Cells(Rows.Count, "B").End(xlUp).Offset(1)
Workbooks(read_file).Close
read_file = Dir()
Loop
Application.ScreenUpdating = True

End Sub

デバッグを覚えましょう。
エクセルVBAでデバッグをする 一例
https://www.excelspeedup.com/vbadebug/

検証してみてください
この回答への補足あり
    • good
    • 0
この回答へのお礼

ご回答ありがとうございます。
こんなにもすっきりした仕上がりにできるんですね。
実行してみたところ動きもスムーズで驚きました。

1点、すみません。
コピーしたいセルを「C10以下最終行までの中で値が入っているセル」と表現してしまったのですが、正しくは「C10〜R24以下最終行までの中で値が入っているセル」でした。
その場合、
Range("C10", Cells(Rows.Count, "C").End(xlUp)).Copy _
のカッコ内が変わると思うのですが、どこに何を入れたらいいのでしょうか。
("R24", Cells(Rows.Count, "R").End(xlUp))を入れ込むのでしょうか?

お礼日時:2021/09/27 08:57

こんにちは


ここまで書いてコードの意味を理解しているのなら
ご質問の答えは自力で解決できると思いますが、、、
書いているコードの意味を今一度確認して理解するようにしましょう。


>・コピー範囲  「C10からR24」ではなく、「C10以下最終行までの中で値が入っているセル」に変更したい
input_end_row = Range("A65536").End(xlUp).Row 書き直すと
input_end_row = Cells(Rows.Count, "A").End(xlUp).Row

これA列の値のある再下行№なので AをCに変更すれば良いと思います。この変数の使い回しをしないなら、変数に代入せずに
Range("C10", Cells(Rows.Count, "C").End(xlUp)) 
でC10~値のある最終行までとなります。

>・貼り付け先セル
 集約シートのB2セル以下にデータを貼り付けたい
上記と同様ですが、値のある次の行となるので行№で行っている場合は+1
セルを移動する場合はOffsetを使用します
ThisWorkbook.Sheets("集約").Activate
Range("B" & output_end_row + 1).Select

offset:
ThisWorkbook.Sheets("集約").Cells(Rows.Count, "B").End(xlUp).Offset(1)


コピーメソッドの場合、Destinationを使用して貼り付け先を指定するのが良いかと
Range("C10", Cells(Rows.Count, "C").End(xlUp)).Copy _
Destination:=ThisWorkbook.Sheets("集約").Cells(Rows.Count, "B").End(xlUp).Offset(1)

ちなみに
開いたブックは必ずお目当てのシートが表示されるのでしょうか?
この部分の対策は必須だと思います。
また、Activate、Select、などは集約出来そうに思いますね
この回答への補足あり
    • good
    • 0
この回答へのお礼

誤って質問の補足に書いてしまったので再投稿します。
--------------------------------------------------------------
さっそくご回答いただきありがとうございます。
完全に私の知識不足で申し訳ないのですが、教えていただいたコードを元のどの部分に差し替えればよいか教えていただくことはできますか?

また、集約シートB2から貼り付けたいという部分ですが、1行目に項目名、A列にNo.を入れてある状態であっても(Rows.Count, "B")を使うことはできますか?

開いたブックは目当てのシートが表示されるのかという点については、sheet1以外作成しないようなルールにしようと思っていますが問題ないでしょうか。

お礼日時:2021/09/24 18:13

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング