Use sort method to sort ranges based on their headings using vba

[kris nuvio]

Hi..

I have several ranges in column B to D, and the heading of each range is in column A.
How can I sort the ranges based on their heading using vba?
The headings are text that i'd like to sort alphabetically..and i need the numbers under each heading needs to stay as they are.

I attached an example showing what my original document looks like..

Thank you in advance, Kris

[OllieB]

kris,

the only way I can think of is:
step 1: copy the header value to the applicable "number rows" belonging to that header (so basically fill A2 to A6 with value "AB", and repeat this for headers "KL" and "CD")
step 2: sort the entire range on column A only
step 3: remove the values in column A for those rows where the cell in column B is not blank

[OllieB]

Kris,

In VBA this would look like

   Public Sub SortHeaderRanges()
   
   '#
   '# declare private variables
   '#
      Dim lngMaxRow As Long
      Dim lngRowNumber As Long
      Dim strHeaderValue As String
      
   '#
   '# for the data on worksheet Sheet1
   '#
      With ThisWorkbook.Worksheets("Sheet1")
         lngMaxRow = .Cells(.Rows.Count, "B").End(xlUp).Row
         
      '#
      '# duplicate header values
      '#
         For lngRowNumber = 1 To lngMaxRow
            If LenB(.Cells(lngRowNumber, "A").Value & "") > 0 Then
               strHeaderValue = .Cells(lngRowNumber, "A").Value
            Else
               .Cells(lngRowNumber, "A").Value = strHeaderValue
            End If
         Next lngRowNumber
   
      '#
      '# sort the entire range
      '#
      
         With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=Cells(1, "A").Resize(lngMaxRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .SetRange Cells(1, "A").Resize(lngMaxRow, 4)
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
         End With
         
      '#
      '# remove the header values that were introduced in step 1 for sorting purposes
      '#
         For lngRowNumber = 1 To lngMaxRow
            If LenB(.Cells(lngRowNumber, "B").Value & "") > 0 Then
               .Cells(lngRowNumber, "A").Value = vbNullString
            End If
         Next lngRowNumber
         
      End With
      
   End Sub

[kris nuvio]

Thank you Ollie!

That might be a good way of solving my problem.
I will test this as soon as I can to see if it works..

[kris nuvio]

The code works perfect, but there's still one thing:
How do I move the whole row with the sort method in the bottom of your code?
Cause I have more info in other columns that should follow the "A"-cell

[OllieB]

Sorry, but i do not understand your question. Can you please rephrase?