Sorting

[paradise]

Hi,
I want to sort alphabetically in sheet1 and leaving a single space when there is change in alphabet.

 

[Hui]

You could do a Sort on Type A-Z
Then do a SubTotal, Count on Type

Then manually go an remove all the subtotals

Otherwise you will need some VBA

 

[Chihiro]

Here's the code.

Sub SortSpace()
Dim tRange As Range
Dim cCell As Range
Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("Sheet1")
Set tRange = ws.Range("B2").CurrentRegion
Set cCell = ws.Range("B2")
'Sort based on Column B in ascending order
tRange.Sort Key1:=cCell, Order1:=xlAscending, Header:=xlYes

'Starting from B4 check if first letter of cell value = the one above
'If yes move on, if not insert row and stop when you reach empty cell
i = 4
Do Until Cells(i, 2) = ""
    If Left(Cells(i, 2), 1) = Left(Cells(i - 1, 2), 1) Then
        i = i + 1
    Else
        Cells(i, 2).Select
        Selection.EntireRow.Insert
        i = i + 2
    End If
Loop

End Sub