Sort data in Excel with blank rows?

[JohnnyBeGood]

Hi all,

I've tried searching here and spent few hours trying myself but I cannot find solution how to sort when there's blank rows. It always removes spaces in between.
I need to sort column B along with text that is on the same row.
Any suggestions?

Not sorted:

Sorted:

TIA!

 

[footoo]

Do you want a manual solution or a VBA solution?

 

[JohnnyBeGood]

Thanks for the reply!

My friend would prefer manual solution.

 

[footoo]

Using the data you provided :
• Select B9 to B20
• Go to SpecialCells/Blanks
• Expand the selection by Ctrl + select B20
• Type =B9 and press Ctrl+Enter
• Select B9:D20 and sort by column B
• Select B9:B20 and go to SpecialCells/Formulas
• Clear contents

Here's a macro too :

Sub v()
With Range("B9:B" & Cells(Rows.Count, 2).End(3)(2).Row)
    .SpecialCells(xlCellTypeBlanks).Formula = "=B9"
    .Resize(, 3).Sort Key1:=Range("B9"), Order1:=xlAscending, Header:=xlNo
    .SpecialCells(xlCellTypeFormulas).ClearContents
End With
End Sub

 

[JohnnyBeGood]

I'm getting stuck on "Expand the selection by Ctrl + select B20" because once I do "SpecialCells/Blanks" B20 is already selected?

VBA Code works great!

 

[footoo]

I'm getting stuck on "Expand the selection by Ctrl + select B20" because once I do "SpecialCells/Blanks" B20 is already selected?

If B20 is already selected, just skip that step

 

[JohnnyBeGood]

Looks like I'm doing it wrong. Once I do this step "Type =B9 and press Ctrl+Enter" I get below error. The purpose is to have all blank cells selected up to B20?

 

[footoo]

The purpose is to select the blank cells in B9:B20 and then put a formula in each cell that returns the same value as the cell above.

 

[JohnnyBeGood]

Got it. What am I doing wrong?

 

[footoo]

Got it. What am I doing wrong?

I have no idea. What is in B10 that returns #REF! ?