[VBA] Sort values in column with respect to blank rows

[Ideal Being]

Hi,

I have a table which can look like this (data are simplified) after some macros are activated:

Column 1	Column 2	Column 3	Column 4
1	A
		cat
3	C	dog
	D
		bird

<tbody>
</tbody>

I have a userform with listbox where are values A-Z and following logic:

1. Select multiple items from listbox
2. Click add button to add them into active sheet
3. Delete duplicity (if you add let's say A and then you want to add it again, it does NOT add it - this can guarantee unique values)
4. Sort whole table by column 2 in alphabetical order
5. Add 2 rows for each row which has value in column 2

The problem is when the macro is activated second time (let's say I want to add value B to column 2), the sort mechanism obviously does not work as it sorts blank cells:

'Sorts test in ascending order by Column C in Range B5:J1000
Columns("C:C").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range _
    ("C1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
With ActiveSheet.Sort
    .SetRange Range("B5:J1000")
    .Header = xlNo
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

Is there a way to tell the sort mechanism that it needs to expand selection and take all 3 rows related to value in column 2? So the final table after adding B into column 2 would look like this:

Column 1	Column 2	Column 3	Column 4
1	A
		cat
2	B
3	C	dog
	D
		bird

<tbody>
</tbody>

All suggestions are appreciated. Thanks to everyone for your time and patience!

 

[Ideal Being]

Is there a chance there is better way than following solution?

Before sorting:

1. When there is value in column 2, copy it into next 2 rows
2. Sort table based on column 2
3. Delete values added in step 1 (however I am not sure how to implement this)

My first though was to merge cells in column 2 (cell with value with 2 following rows) and use it to sort but it is not possible to sort table when cells are not the same size..


Any suggestions appreciated, any advice or piece of VBA can save me lots of time. Thanks in advance to everybody investing time into my problem!