【Excel】空白行の下の行にデータがあれば空白行の隣に移動、次のはその隣に移動、てできますか？

excelこんなデータがあります。.....は連続ブランクを教えてgooが認識しないためなので、ブランクだと思ってください。

　A列
1
2りんご
3みかん
4　
5ばなな
6めろん
7そば
8

上記をこのようにしたいです。

A列.......|B列....|C列....|D列
1　.........|りんご|みかん
2　.........|
3　.........|
4　.........|ばなな|めろん|そば

言葉で説明しますと　
①ブランク行の下行にデータがあれば、ブランク行の隣に移動させる
　（A2のデータをB1に移動する）
②その下にもデータがあれば、①で移動させたデータの隣に移動させる
　（A3のデータをC1に移動する）
③その下がブランクであれば何もしない
④その下にデータがあれば、ブランク行の隣に移動させる
　（A5のデータをB4に移動する）
⑤以降を繰り返す

これを、関数でもVBAでもいいのである程度自動で行いたいです。

なぜこんなややこしいことをしたいかといいますと、先の例のブランクとブランクの間の
データは一つのデータなので、横並びにして管理したいのです・・・
また、ブランクの位置がずれてしまうとデータとして使えないので
試行錯誤している次第です。

以上、ご教授よろしくお願い申し上げます。

No.1 回答者： tatsumaru77 回答日時： 2021/01/14 18:22

マクロで実行時、A列のみデータが設定されており、ほかの列はデータが未設定である、という前提で良いのでしょうか。

No.2 回答者： mike_g 回答日時： 2021/01/14 18:29

》 試行錯誤している次第です。

》 ご教授よろしくお願い…
「ご教授」ねェ～、(^_^)
恥かし乍ら、私の“滅茶苦茶ゴリオシ”「試行錯誤」の結果です。
添附圖參照(Excel 2019)

Sheet2 に於いて、
１．下記の各セルに夫々右側の式を入力後、範圍 F2:H2 を下方に
￣￣オートフィル
￣￣ A2: =COUNTIF(A$1:A1,"")
￣￣ B2: =SMALL(IF(A:A="",ROW(A:A)),ROW(A1))
￣￣ C2: =G3-G2
￣￣ D2: =C2-1
￣￣ 【御斷はり】B2の式のみ、必ず配列數式として入力
Sheet1 に於いて、
２．次式を入力したセル B2 を
￣￣ =IF(COUNTIF(Sheet2!$B$2:$B$10,ROW()-1),IF(COLUMN(A1)>INDEX(Sheet2!$D$2:$D$8,Sheet2!$A2),"",OFFSET($A2,COLUMN(A1)-1,)),"")
３．範圍 B列以右を選擇⇒Ctrl+C⇒當該選擇範圍内でマウスの
￣￣右クリック⇒[貼り付けのオプション]直下の[123](値)アイコン
￣￣をツン⇒A列を選擇⇒Deleteキーを「エイヤッ!」と叩き附け

No.3 回答者： tatsumaru77 回答日時： 2021/01/14 18:32

上記の例で、もし、A1にデータがあった場合はA1,A2,A3のデータは、移動せず、そのままで良いのでしょうか。

No.4 回答者： mike_g 回答日時： 2021/01/14 18:34

[No.1]誤謬訂正、

ステップ１内の
》 範圍 F2:H2 を下方に…
の「F2:H2」は「A2:D2」に訂正します。m(_._)m

No.5 回答者： goomania 回答日時： 2021/01/18 10:05

もし、ご質問者のご希望を関数で実現するとなると、関数では自身を書き換えることはできませんので、

sheet1に元データがあるとして、sheet2に移動後のデータが作成されるものとして、考えてみました。
ご質問者の説明によれば、移動後のデータのA列は常に空白セルということになりますので、sheet2のA列は空白のまにしておきます。
sheet2のB1に以下の数式
=IF(IF((COLUMN()>2)*(A1="")+(Sheet1!$A1<>""),"",INDEX(Sheet1!$A:$A,COLUMN()+ROW()-1,1))=0,"",IF((COLUMN()>2)*(A1="")+(Sheet1!$A1<>""),"",INDEX(Sheet1!$A:$A,COLUMN()+ROW()-1,1)))
を記述し、右方向へコピーします。sheet1の空白と空白で挟まれたデータの個数の最大値分コピーすることになります。
同様に下方向にもコピーします。判り易くするため、データのある部分にはセルに色をつける「条件付き書式」を設定してみました。
添付画像を参照してください。
また、仮にVBAで実現するとすれば、以下のようなものになると思います。

Option Explicit

Sub sumple()
Dim target As range
Dim i As Long
Dim k As Long
Dim e_row As Long

e_row = Cells(Rows.Count, 1).End(xlUp).Row
k = 0
For i = 1 To e_row
Set target = Worksheets(1).Cells(i, 1)
If Worksheets(1).Cells(i, 1).Value = "" Then
k = 0
Else
k = k + 1
If i - k > 0 Then
target.Offset(-k, k).Value = target.Value
target.Clear
End If
End If
Next
Set target = Nothing
End Sub

なお、このVBAはtatsumaru77さんのご指摘のとおり、

>もし、A1にデータがあった場合はA1,A2,A3のデータは、移動せず、そのまま

となります。

だたひとつ疑問なのは、ご質問者は

>また、ブランクの位置がずれてしまうとデータとして使えないので

とおっしゃっていますが、データも移動した時点でA列は全てプランクになり、もともとのブランクの位置は変らなくても、新たにブランクが発生してしまいます。
それでよいのでしょうか?