２つのシートの値を隙間なく連結させたい。

Question

###前提・実現したいこと
ここに質問したいことを詳細に書いてください
ExcelVBAで各シートの値を一つのシートに繋げるシステムを作っています。

###発生している問題・エラーメッセージ
aシートとbシートの値をaシートが上になるように繋げてAllシートに反映させるとき、なぜかA6セルに空白セルができてしまいます。この空白を無くし、シートの値を繋げて表示させたいです。

###該当のソースコード

Option Explicit
Dim i

Sub test()
    For i = 1 To Worksheets("a").Cells(1, 1).End(xlDown).Row
        Worksheets("All").Cells(i, 1).Value = Worksheets("a").Cells(i, 1).Value
    Next
    
    For i = 1 To Worksheets("b").Cells(1, 1).End(xlDown).Row
        Worksheets("All").Cells(i + Worksheets("All").Cells(1, 1).End(xlDown).Row, 1).Value = Worksheets("b").Cells(i, 1).Value
    Next
End Sub

分かる方、ご教示願います。

Answer 1

まず、「eee」が5行目に出るのは、i=1、.End(xlDown).Row=4なので、足して5になるからです。
次に「fff」ですが、i=2、.End(xlDown).Row=5になり足して7になるため、6行目には出力されません。

では、なぜ7行目以降が続けて出力されるのかといいますと、
.End(xlDown).Rowは、下方向に最終行を取得します。最終行というのは空白セルの前の行です。
今回の場合、６行目が空白セルのため、.End(xlDown).Rowは常に5を返却します。
なので、iのインクリメントだけで出力されていきます。

修正するのであれば、bシートを出力する前に、
aシートを出力した時点での最終行を取得します。
bシートを出力する際は、iと取得した最終行を足した値で出力します。

Option Explicit
Dim i
Dim lastRow

Sub test()
    For i = 1 To Worksheets("a").Cells(1, 1).End(xlDown).Row
        Worksheets("All").Cells(i, 1).Value = Worksheets("a").Cells(i, 1).Value
    Next

    lastRow = Worksheets("All").Cells(1, 1).End(xlDown).Row

    For i = 1 To Worksheets("b").Cells(1, 1).End(xlDown).Row
        Worksheets("All").Cells(i + lastRow , 1).Value = Worksheets("b").Cells(i, 1).Value
    Next
End Sub

Answer 2

2回目のループの Worksheets("All").Cells(1, 1).End(xlDown).Row の値がループ中に 4 から 5 に変化しているためです。
ブレイクを設定して、F8 で実行していくと、値の変化がわかります。

2回目のループが始まる前に、 Worksheets("All").Cells(1, 1).End(xlDown).Row の値を別の変数に代入しておくことで、値の変化を防げます。

Sub test2()
    For i = 1 To Worksheets("a").Cells(1, 1).End(xlDown).Row
        Worksheets("All").Cells(i, 1).Value = Worksheets("a").Cells(i, 1).Value
    Next

    '' 現状の最終行番号を変数に保存。    
    Dim rowOffset As Long
    rowOffset = Worksheets("All").Cells(1, 1).End(xlDown).Row

    For i = 1 To Worksheets("b").Cells(1, 1).End(xlDown).Row
        Worksheets("All").Cells(i + rowOffset, 1).Value = Worksheets("b").Cells(i, 1).Value
    Next
End Sub