In some of my VBA projects, I need to loop through a range of cells and insert a blank row between groups of data, based on a value change in a specific sorted column (Excel dataset is sorted by the respective column). This gives the user a clear visual look at data groupings, and allows them to focus their attention on the data in the way I intend. In this post, I will show you how to insert a blank row at each value change in a sorted column.
Excel VBA – Insert Row at Value Change in Column – Setup
Here is the macro code:
Dim c As Long
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
For c = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
If Cells(c - 1, 1) <> Cells(c, 1) And (c - 1) <> 1 Then _
Cells(c, 1).Resize(1, 1).EntireRow.Insert
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Let’s say you have the following Excel dataset:
Now, you would like to insert a blank row, at each change in the column “Week”. In other words, you would like to insert a blank row between Weeks 1 & 2, 2 & 3 and 3 & 4.
After executing the VBA code above, your Excel dataset will look like this:
The resulting dataset clearly breaks up the dataset into groups that draws the audiences attention to smaller datasets within the overall table.
Feel free to modify the code to suit your needs.