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.
'Turn off ScreenUpdating.
Application.ScreenUpdating = False
'Sort the table by ClientName in ascending order.
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
For xRow = LastRow To 5 Step -1
If Cells(xRow, 1).Value <> Cells(xRow - 1, 1).Value Then _
'Turn ScreenUpdating on again.
Application.ScreenUpdating = True