Tom’s Tutorials For Excel: Inserting an Empty Row Between Each Change in Value

Tom’s Tutorials For Excel: Inserting an Empty Row Between Each Change in Value

Here’s how you can insert a row between changes in items in your table range. In the picture, you prefer your data to be sorted by Client Name in column A. To make the finished table more readable, you want an empty row at each change in the Client Name.



Based on the pictured example, this macro does that, with explanatory comments in green.

Sub Sort_Separate_ClientName()
'Turn off ScreenUpdating.
Application.ScreenUpdating = False
'Sort the table by ClientName in ascending order.
Range("A3").CurrentRegion.Sort _
Key1:=Range("A4"), Order1:=xlAscending, Header:=xlYes
'Declare a Long variable for the last row in column B.
Dim LastRow As Long
'Determine the last row of data in column A.
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Declare a Long type variable for evaluating each row.
Dim xRow As Long
'Loop through each ClientName item in column A of the table.
'When the item being evaluated is not the same as the item
'in the row above it, that means the client name is different.
'Insert an empty row at that change.
'Notice, work from the bottom of the row upwards because you are
'inserting rows.
For xRow = LastRow To 5 Step -1
If Cells(xRow, 1).Value <> Cells(xRow - 1, 1).Value Then _
Rows(xRow).Resize(1).Insert
Next xRow
'Turn ScreenUpdating on again.
Application.ScreenUpdating = True
End Sub