How to insert blank rows where the value changes

I got the following question from Jonathan this week:
...
Don't know if this can be done at the moment, perhaps you could advise...

I have a range of cells and I want to select ALL the cells/rows in the selection where the value changes. I can select the first cell where the value changes using 'Select Cells' (3) then 'Select cells in range where value changes' (11) but want it to do a bit more.

I will then insert blank rows where the value changes.

Hope you can help. Keep up the good work.
...

Inserting a blank row after the value changes can be done with the following utility (which I have always found it difficult to use a good name for): Format » Detect and visualize adjacent data/group changes

Select the column with the values/titles that change at some point and then start the following utility:
ASAP Utilities » Format » Detect and visualize adjacent data/group changes
Then choose "empty row" and press the "start" button:
Insert a blank row where the value changes

3 Responses to “How to insert blank rows where the value changes”

  1. If the intent of inserting a row is just for appearence sake, I use a column off to one side of the Pint Area with a simple IF statement in each cell

    =IF(A1A2,".[Alt][Enter].","..") (press [Alt][Enter] don't type it)

    then "check" the Wrap text option under Format | Cells… Alignment

    Before printing, select this control column and press [Ctrl][B] to toggel bold font on or off, those rows meeting the criteria will grow to double size and give the output the appearance of an extra line without breaking up the table. The advantage to this is that as the table changes it will re-adjust the row heights any time you toggel the control column.

  2. =IF(A1=A2,”.[Alt][Enter].”,”..”)

    The equal sign did not show up on my reponce…???

  3. Hello Mike,

    Nice tip.
    Your comment was held for moderation. This is because because there are a lot of people and robots trying to post their spam messages here.

    Kind regards,
    Bastien

Pinterest