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
I am trying to compare data in column D. What values in this code need to be changed?
If Cells(xRow, 1).Value Cells(xRow – 1, 1).Value:-
“, 1) ” ->”, 4) “
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.
No need to add column for sequent number
For a lot of rows I would not bother with a pivot table. Advanced Filter is easier and cleaner to get unique items.