Macro to loop worksheets and copy and merge values to single cell

[Flea2]

I am just getting started with VBA and my need described below is beyond my current knowledge - I would greatly appreciate anyone who could advance my learning to achieve the following:

In the uploaded minisheet I show the "before and after" of what I need a VBA routine to do.

Specifically, in the Range A1:B9, I need the code to:
- check each row in turn, and where
the value in column A is = "", copy the value in the adjacent cell in Column B and
Add it (concatenate) to the existing value (on a new line) in the cell immediately above it and then
Delete the complete row that had the value "" in Column A.

Thanks in advance for your help/coaching.

Merge Values on blank condition .xlsm
	A	B	C	D	E	F	G
1	01-Nov-21	Description 1of2	< BEFORE		AFTER >	01-Nov-21	Description 1of2 Description 2of2
2		Description 2of2				02-Nov-21	Description 1of2 Description 2of2
3	02-Nov-21	Description 1of2				02-Nov-21	Description 1
4		Description 2of2				03-Nov-21	Description 1
5	02-Nov-21	Description 1				04-Nov-21	Description 1of2 Description 2of2
6	03-Nov-21	Description 1				05-Nov-21	Description 1
7	04-Nov-21	Description 1of2
8		Description 2of2
9	05-Nov-21	Description 1
Data-before&after

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub Flea()
   Dim Rng As Range
   
   For Each Rng In Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
      Rng.Offset(-1, 1).Value = Join(Application.Transpose(Rng.Offset(-1, 1).Resize(Rng.Count + 1).Value), vbLf)
   Next Rng
   Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[Flea2]

Hi & welcome to MrExcel.
How about

Sub Flea()
   Dim Rng As Range
  
   For Each Rng In Range("A1:A" & Range("B" & Rows.Count).End(xlUp).Row).SpecialCells(xlBlanks).Areas
      Rng.Offset(-1, 1).Value = Join(Application.Transpose(Rng.Offset(-1, 1).Resize(Rng.Count + 1).Value), vbLf)
   Next Rng
   Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

Brilliant - thank you. I will be analyzing the solution to accelerate my learning!

 

[Fluff]

You're welcome & thanks for the feedback.