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

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
5 comments on “Tom’s Tutorials For Excel: Inserting an Empty Row Between Each Change in Value
  1. Dawn says:

    I am trying to compare data in column D. What values in this code need to be changed?

    • Hannah Li says:

      Key1:=Range(“A4”):-
      “A4″ ->”D4”
      If Cells(xRow, 1).Value Cells(xRow – 1, 1).Value:-
      “, 1) ” ->”, 4) “

  2. Hannah Li says:

    If more rows data, e.g. 50000, much time would be used, suggest using operation…pivot table to obtain unique items (UI) for separate…add the UI to the database…insert column for sequential number (SQ) …sort by UI & SQ…delete SQ column…filter by any field other than UI…delete added UI…clear filter.

Leave a Reply to Dawn Cancel reply

Your email address will not be published. Required fields are marked *

*