Excel VBA – Insert Row at Value Change in Column

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:

Let’s say you have the following Excel dataset:

Excel VBA - Insert Row at Value Change in Column

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:

Excel VBA - Insert Row at Value Change in Column

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.

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

 

Leave a Comment