Vba - Sorting variable range of cells

[vrobinson0304]

I have a spreadsheet that has multiple rows of data that are grouped between a header and blank cell. I am trying to write a macro to run through column B to search for the headers (there are 5 possible headers) and select the cell under it until the last cell before a blank cell and sort in ascending order. I am new to vba and can usually peice together code, but am having a roadblock here.

header1
a
b
c
blank cell

header2
x
y
z
blank cell

Thank you in advance!

 

[John_w]

Try this:

Sub SortB()

    Dim headers As Variant, header As Variant
    Dim headerCell As Range, blankCell As Range
    
    headers = Array("header1", "header2", "header3", "header4", "header5")
    
    For Each header In headers
        Set headerCell = Columns("B:B").Find(What:=header, After:=Range("B1"), LookIn:=xlFormulas, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
        
        If Not headerCell Is Nothing Then
            Set headerCell = headerCell.Offset(1, 0)
            Set blankCell = Columns("B:B").Find(What:="", After:=headerCell, LookIn:=xlFormulas, _
                LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
                MatchCase:=False, SearchFormat:=False)
            
            Range(headerCell, blankCell).Sort Key1:=headerCell, Order1:=xlAscending, header:=xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
       
        End If
        
    Next

End Sub

 

[vrobinson0304]

Thank you this works. However, I need it to sort the entire rows. Right now it is only sorting the cells in column B. Please advise.

Thanks again!

 

[John_w]

The Macro Recorder helps with the syntax for sorting rows. Change the Sort statement to:

            Rows(headerCell.Row & ":" & blankCell.Row).Sort Key1:=headerCell, Order1:=xlAscending, header:=xlGuess, _
                OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

 

[vrobinson0304]

Thank you! This worked!!!