Sorting and then adding blank rows to seperate

[senken]

Dear Excel Experts,

Forgive me if this has already been asked, but I've searched for a solution for my problem without much success.

Everyday at work, I have to do a data sort by three categories. After that, I have to manually put in extra blank rows to separate one specific column.

Is there an easier way to do this?

Example:

SINGAPORE
SINGAPORE
THAILAND

Is there a macro or formula for putting a blank row between Singapore and Thailand for just one specific column?

Thank you in advance.

Tony

 

[Hooi]

Dear Excel Experts,

Forgive me if this has already been asked, but I've searched for a solution for my problem without much success.

Everyday at work, I have to do a data sort by three categories. After that, I have to manually put in extra blank rows to separate one specific column.

Is there an easier way to do this?

Example:

SINGAPORE
SINGAPORE
THAILAND

Is there a macro or formula for putting a blank row between Singapore and Thailand for just one specific column?

Thank you in advance.

Tony

Do singapore and singapore need blank row between it?and also Thailand first then singapore come after need the blank row too?

 

[AlphaFrog]

This inserts blank rows when two adjacent cells in column C are different from each other. You can change the column this works on by change in the "C"s in the code...

Sub Insert_Rows()

    Dim Lastrow As Long, r As Long
       
    Lastrow = Cells(Rows.Count, "[COLOR="Red"]C[/COLOR]").End(xlUp).Row - 1
    
    Application.ScreenUpdating = False
    For r = Lastrow To 2 Step -1
        If Cells(r, "[COLOR="Red"]C[/COLOR]") <> Cells(r + 1, "[COLOR="Red"]C[/COLOR]") Then Rows(r + 1).Insert
    Next r
    Application.ScreenUpdating = True
    
End Sub

 

[WRL]

Hi Senken,

The following code should do what you require, but it does make a few assumptions. See my comments below:

Sub InsertSpace()
    Dim lRow As Long
    Dim lCol As Long
    
    With ActiveSheet
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=Range("A2:A17"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    End With
    With ActiveSheet.Sort
        .SetRange Range("$A$1:$A$18")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    lRow = 1
    lCol = 1
    While ActiveSheet.Cells(lRow, lCol) <> ""
        lRow = lRow + 1
        While ActiveSheet.Cells(lRow, lCol) = ActiveSheet.Cells(lRow - 1, lCol)
            lRow = lRow + 1
        Wend
        If ActiveSheet.Cells(lRow, lCol) <> "" Then
            ActiveSheet.Cells(lRow, lCol).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
            lRow = lRow + 1
        End If
    Wend
End Sub

Assumptions:
1. The code will work for Excel 2007 and later
2. Change the references to Range() to incorporate your range
3. I assume your data has headers and that this is in row 1. Change the lRow = 1 to indicate the row where your data starts
4. I assume the cities are in column 1 (column "A"). Change the lCol = 1 accordingly as well.
5. You mentioned you sort on three criteria, but did not specify the rest of the criteria. You can add the relevant columns below this line in a similar manner: .Sort.SortFields.Add Key:=Range("A2:A17"),

Hope it helps